Toad World® Forums

Trigger with logic?

Can someone please give me an example of a Oracle trigger that implements logic? I have three separate triggers that can fire from ON INSERT on the same table. The triggers work individually, but I’d like to combine them so that the trigger can determine which of the three actions to take based on the data that was INSERTed on the row written to the table SOME_TABLE. I’m new with the server side stuff and I’m thinking surely this can be done, but I’m having trouble finding the correct syntax.

I have something very similar to the following in each of the three triggers which seems to work fine. I hope to get the month (mth) and year fields from SOME_TABLE as well as the action to take.

create or replace trigger GLENN_TRIGGER

AFTER INSERT ON SOME_TABLE

BEGIN

update another_table l set l.field1 =

(select distinct n.field1 from still_another_table n where (l.field2 = n.field2)

and (l.mth = 4) and (l.year = 2014)) where (l.mth = 4) and (l.year = 2014);

delete from some_table;

END;

I would not expose app logic in triggers whenever I’m forced to use that technique of programming (as, for instance, integration some new interfaces to ready made solution which cannot be modified).

Otherwise, triggers are pretty simple but be careful not to fall into mutating trigger situation.