Trigger

Fons Reijsbergen · *nix forums beginner Joined: 10 Feb 2005 Posts: 2

Hello,

I try to create a trigger that do the following:
A table has records from some book-collections, A book can be in one or more
collections.
There is a second (event)table for each collection, this hold the records
that are updates, deleted or inserted. This second-table is used by a
program to index the books, each collection have it's index. We can not
modify the working of the program.
So when a record is inserted this event must be inserted in one of the
event-tables. I can write a trigger that looks for the collection key en
insert then in the event-table a record:
if Biblio_DB = 1
insert into event_title1 values.....
end if
if Biblio_DB = 2
insert into event_title2 values.....
end if

But the collections are changing, so when a collection is deleted the
trigger must be alterd also when a collection is removed or renamed. This is
not what I want.
I know that this is not working, but I'm looking for a methode to do this:
CREATE TRIGGER make_event
AFTER INSERT ON BIBLIO
FOR EACH ROW
BEGIN
FOR EACH $i IN (Select distinct(COL_TEXT) from COLLECTION)
LOOP
IF :new.BIBLIO_DB IN (Select COL_DB from COLLECTIONS Where Col_TEXT
= $i
insert into EVENT_$i values(:new.Biblio_NR, 2)
en if
END LOOP
END

Now I get the name ande the values out a table so I do not have to modify
the trigger is a collaction changed. Is it posible to do this in some way?
Have someone an idee?

The tables I have look like this:
The trigger is on the table BIBLIO:
Biblio_NR number
Biblio_DB number
Biblio_TEXT char
Biblio_STATUS number

A have a table COLLECTION:
Col_DB number
Col_TEXT char

The value's of this table look like:
1, Title1 (or EVENT_Title1 if that is esay in the insert statement)
2, Title1
3, Title2
3, Title3

Then we have 3 event-tables, EVENT_TITLE1, EVENT_TITLE2, EVENT_TITLE3:
EVENT_ID number
EVENT_STATUS number

Thanks
F.Reijsbergen
fons.reijsbergen@kb.nl

Google