CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Arav
Arav

Reputation: 5247

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?

Upvotes: 3

Views: 2765

Answers (4)

NARESH
NARESH

Reputation: 1

In Oracle 10g we do not control the triggers that are created on same timing. It is executed randomly. So we cannot say which trigger is fired first. To overcome this problem, Oracle 11g introduced FOLLOWS CLAUSE. Using this we can control the execution order.

Upvotes: -1

Justin Cave
Justin Cave

Reputation: 231661

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;

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.

Upvotes: 4

DCookie
DCookie

Reputation: 43523

No order to trigger firing can be relied upon in 10g beyond the normal before statement, before row, after row, after statement order. In 11g a new FOLLOWS clause was added to the CREATE TRIGGER statement.

Upvotes: 3

Related Questions