Re: Trigger

Fons Reijsbergen wrote:
> 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
>
>
>
>

look up execute immediate, or dbms_sql on older versions

-- 
Regards,
Frank van Bortel