Comquack Comquack - 10 months ago 39
SQL Question

Oracle Triggers, reliant on a key generated by a second trigger

I have question about triggers. For my database assignment, I have to create a trigger that automatically generates an ID number, which I have done.
The problem is a second trigger needs to be made that also acts on the same data. Both are inserts. Since they have to be separate, I am not sure how to make this work.
From what I have been taught the way to make a trigger act on the most recent addition is to use:


where ID is the primary key, but this does not work for me when the ID is being generated by a trigger. Is there a method of creating a trigger that acts on the most recent row added to the table that does not reference the primary key?

Answer Source

It is not clear why you need two triggers but let's assume it really makes sense (which I doubt).

Does the first trigger generates the ID from a sequence? In this case you can use value CURRVAL of the sequence. This pseudocolumn returns the current value of a sequence without increasing the value, see Sequence Pseudocolumns

If this does not fit your needs you can write a procedure which is then called be the trigger.

Would be like this:

create procedure PROC(aRow in ROWID) as

create first_trigger ....
   ... whatever is needed at first trigger.

create second_trigger ....
   ... whatever is needed at second trigger.

Both triggers would operate on the same row. You can also write current row values into a PL/SQL variable and process them by a Statement-Trigger (i.e. no row-level-trigger)