Regarding trigger sequence
Have two triggers on a table. One trigger is executed when there is a insert or update for each row in the table. Second trigger is executed when there is a update for each row in the table. Which trigger gets executed first in ORACLE 10G when there is a update statement on a row in the table. Is there any order of execution for triggers in oracle? If so how can i set it?
The order in which the triggers will fire is arbitrary and not something that you can control in 10g. I believe, technically, it goes in the order that the triggers happened to be created but that's certainly not something that you'd want to count on.
In 11g, you can control the firing order of triggers. However you are almost always better off replacing the two triggers with one trigger that calls two stored procedures. So rather than
CREATE TRIGGER trg_1 BEFORE UPDATE ON t FOR EACH ROW BEGIN <<do thing 1>> END; CREATE TRIGGER trg_2 BEFORE UPDATE ON t FOR EACH ROW BEGIN <<do thing 2>> END;
you would be much better served with something like
CREATE PROCEDURE p1( <<arguments>> ) AS BEGIN <<do thing 1>> END; CREATE PROCEDURE p2( <<arguments>> ) AS BEGIN <<do thing 2>> END; CREATE TRIGGER trg BEFORE UPDATE ON t FOR EACH ROW BEGIN p1( <<list of arguments>> ); p2( <<list of arguments>> ); END;
For versions before 11g, no, the order is unspecified. From 10g Release 2 docs:
For enabled triggers, Oracle automatically performs the following actions:
Oracle runs triggers of each type in a planned firing sequence when more than one trigger is fired by a single SQL statement. First, statement level triggers are fired, and then row level triggers are fired.
Oracle performs integrity constraint checking at a set point in time with respect to the different types of triggers and guarantees that triggers cannot compromise integrity constraints.
Oracle provides read-consistent views for queries and constraints.
Oracle manages the dependencies among triggers and schema objects referenced in the code of the trigger action
Oracle uses two-phase commit if a trigger updates remote tables in a distributed database.
Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.