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?

Answers


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.


Need Your Help

How use option Kd Ks Ka

c++ opengl

how use/draw "Kd Ka Ks"?

F# Parsing CSV file - find which line is wrong

parsing csv f# filehelpers

I wrote this script from some resources I found. It's working but I some files I have problem. I am new in F# so how can I change line with FileHelpersException to get exact line where is the probl...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.