Trigger

From: Fons Reijsbergen <fons_at_reijsbergen.nl>
Date: Thu, 10 Feb 2005 11:44:21 +0100
Message-ID: <420b3d12$0$68838$cd19a363@news.wanadoo.nl>

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_at_kb.nl Received on Thu Feb 10 2005 - 04:44:21 CST