Insert trigger

[acidhax]

Okay, so I've got THREE MSSQL Tables.
First table is: Eponyms : ID, NAME, DESCRIPTION, CATEGORIES
Second table: Categories : ID, CATEGORY
Third is the intersecting table: EponymCategory : ID, EponymID<FK>, CategoryID<FK>

The "Eponyms" table has the list of Eponyms, and the CATEGORIES column is inserted with data like "PSYCH CHEM BIO"
The "Categories" table has the list of categories, "PSYCH", "CHEM", "BIO" listed separately etc..

Now, I need a trigger on the Eponyms table, that would read the "CATEGORIES" column being inserted, and would compare the entries in the "Categories" table... I'm having trouble figuring out how to do this... The Data being inserted into this column is formed like "PSYCH CHEM BIO" -- separated by a space.

I need something like, INSERT INTO EponymCategory (?, ?) WHERE Eponyms.CATEGORIES LIKE %Categories.CATEGORY%

How would something like this be done?

[acidhax]

I've come up with this:

ALTER TRIGGER InsertIntoEponyms 
   ON  Eponyms 
   AFTER INSERT
AS 
BEGIN

INSERT INTO EponymCategory SELECT a.ID, B.ID FROM INSERTED a, EponymCategories b WHERE CHARINDEX(b.Category, a.Categories) > 0;
	SET NOCOUNT ON;
	END
GO

Maybe someone else has a more elegant solution?

[tesuji]

Hi

In principle, the trigger you imagine may look like this:

CREATE TRIGGER InsertIntoEponyms AFTER INSERT ON Eponyms
  REFERENCING OLD AS oldRow NEW AS newRow FOR EACH ROW
BEGIN
  DECLARE Eponyms_CATEGORIES varchar(255);
  -- further declarations ...
  set Eponyms_CATEGORIES = newRow.CATEGORIES; -- get current value of Eponyms.Categories
  /*
  Here should be some code to process Eponyms_CATEGORIES:
  1. parsing Eponyms_CATEGORIE to get all tokens e.g. 'PSYCH', 'CHEM', 'BIO' etc 
  2. Looking up table Categories whether the tolkens already exist there
  3. If not, insert them into table Categories
  4. Finally insert linking data into EponymCategory, to link Eponyms with Categories
  */
END;

Sure, the sketched steps 1 to 4 can be programmed. The serious problem I see is CATEGORIES of Eponyms where you encode data like 'PSYCH CHEM BIO'. Its length and its contains may vary from row to row. So it isn't that easy to decode such string and use its result in sql statements efficiently. You may have a look at my last posting where we have similar decoding problems.

I believe that you could significantly simplify your problem if you improve your data modell (more precisely: simply drop Eponyms.CATEGORIES). I am rather sure that you don't need such a decoding task neither a trigger to carry out the tasks you explained.

I will construct an example to explain this more detailed (can post it Tuesday afternoon). In the meantime I suggest to study the following thoroughly:

There is a true many-to-many relationship between table Eponyms and table Categories. This relationship is made up with "intersection" table EponymCategory you already stated. If so, you don't need column CATEGORIES of Eponyms because all information you need is already stored in EponymCategory. Therefore, you don't need a trigger, and more important, nor a function to parse and disjoin strings like 'PSYCH CHEM BIO'. (btw, this string is a horizontal repeating group, thus your table does not meet first normal form (Edgar Frank Codd's theory)

-- tesu

[tesuji]

Almost forgotten:

>> "Third is the intersecting table: EponymCategory : ID, EponymID<FK>, CategoryID<FK>"

The primary key of this table which makes up the many-to-many relationship must be at least (EponymID,CategoryID). If not, many-to-many relationship would be destroyed. There is no further ID here! This is an example on how to create it:

create table EponymCategory (EponymID integer not null, CategoryID integer not null,
  primary key (EponymID,CategoryID),
  foreign key (EponymID) references Eponyms(EponymID),
  foreign key (CategoryID) references Categories(CategoryID)
);

For rare situations pk(EponymID,CategoryID) appears to be not not unique. If so, we shall discuss it in detail.

-- tesu