Before
Oracle 11g, more than trigger can be created on one table but Oracle doesn't guarantee the order of execution of trigger.
Oracle has introduced the feature in Oracle 11g to execute the trigger
in any order as per requirements.
CREATE TABLE test_order_trigger
(
id NUMBER (10)
, name VARCHAR2 (10)
);
CREATE OR REPLACE TRIGGER test_order_trigger1
BEFORE INSERT
ON
test_order_trigger
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line ('TEST_ORDER_TRIGGER1
first trigger - Executed');
END;
/
CREATE OR REPLACE TRIGGER test_order_trigger2
BEFORE INSERT
ON
test_order_trigger
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line ('TEST_ORDER_TRIGGER2
first trigger - Executed');
END;
/
Let’s
Insert the data into the table, there is no guarantee of the execution of the
order
Insert into test_order_trigger values(1, 'John');
|
Output ->
|
TEST_ORDER_TRIGGER2 first trigger -
Executed
|
TEST_ORDER_TRIGGER1 first trigger –
Executed
|
As
per requirements, trigger1 should be executed first followed by trigger2. For this
we need to specify
Follow Trigger
trigger_name
CREATE TABLE
test_order_trigger
|
(
|
id
NUMBER (10)
|
, name
VARCHAR2 (10)
|
);
|
CREATE OR REPLACE
TRIGGER test_order_trigger1
|
BEFORE INSERT
|
ON test_order_trigger
|
FOR EACH ROW
|
BEGIN
|
DBMS_OUTPUT.put_line ('TEST_ORDER_TRIGGER1
first trigger - Executed');
|
END;
|
/
|
CREATE OR REPLACE
TRIGGER test_order_trigger2
|
BEFORE INSERT
|
ON test_order_trigger
|
FOR EACH ROW
|
FOLLOWS test_order_trigger1
|
BEGIN
|
DBMS_OUTPUT.put_line ('TEST_ORDER_TRIGGER2
second trigger - Executed');
|
END;
|
/
|
Lets look at the
output now :
Insert into
test_order_trigger values(1, 'John');
TEST_ORDER_TRIGGER1
first trigger - Executed
|
TEST_ORDER_TRIGGER2
second trigger - Executed
|
Compound Triggers
A compound trigger allows code for one
or more timing points for a specific object to be combined into a single
trigger. Once a statement ends , state of trigger will be cleared. In previous
release, if we need to perform different functions when any DML happens on the
table this was possible by creating multiple triggers.
Syntax :
CREATE OR REPLACE TRIGGER <trigger-name>
|
FOR <trigger-action> ON
<table-name>
|
COMPOUND TRIGGER
|
-- Global declaration.
|
g_global_variable
VARCHAR2(10);
|
BEFORE STATEMENT IS
|
BEGIN
|
NULL; -- Do something here.
|
END BEFORE STATEMENT;
|
BEFORE EACH ROW IS
|
BEGIN
|
NULL; -- Do something here.
|
END BEFORE EACH ROW;
|
AFTER EACH ROW IS
|
BEGIN
|
NULL; -- Do something here.
|
END AFTER EACH ROW;
|
AFTER STATEMENT IS
|
BEGIN
|
NULL; -- Do something here.
|
END AFTER STATEMENT;
|
END <trigger-name>;
|
/
|
Lets have a look at
the example now
CREATE TABLE
test_compound_trigger (
|
id
NUMBER,
|
description
VARCHAR2(50)
|
);
|
CREATE OR REPLACE
TRIGGER test_compound_trig1
|
FOR INSERT OR UPDATE OR DELETE ON
test_compound_trigger
|
COMPOUND TRIGGER
|
i Number (10):= '100';
|
BEFORE STATEMENT IS
|
BEGIN
|
i := i+10;
|
dbms_output.put_line
('-------------------------------------------------');
|
dbms_output.put_line(' executed before
statement trigger');
|
dbms_output.put_line('value of i is
'|| i);
|
dbms_output.put_line
('-------------------------------------------------');
|
END BEFORE STATEMENT;
|
BEFORE EACH ROW IS
|
BEGIN
|
i := i+10;
|
dbms_output.put_line
('-------------------------------------------------');
|
dbms_output.put_line(' executed before
each row trigger');
|
dbms_output.put_line('value of i is
'|| i);
|
dbms_output.put_line
('-------------------------------------------------');
|
END BEFORE EACH ROW;
|
AFTER EACH ROW IS
|
BEGIN
|
i := i+10;
|
dbms_output.put_line
('-------------------------------------------------');
|
dbms_output.put_line(' executed after each
row trigger');
|
dbms_output.put_line('value of i is
'|| i);
|
dbms_output.put_line ('-------------------------------------------------');
|
END AFTER EACH ROW;
|
AFTER STATEMENT IS
|
BEGIN
|
i := i+10;
|
dbms_output.put_line
('-------------------------------------------------');
|
dbms_output.put_line(' executed after
statement trigger');
|
dbms_output.put_line('value of i is
'|| i);
|
dbms_output.put_line
('-------------------------------------------------');
|
END AFTER STATEMENT;
|
END
test_compound_trig1;
|
/
|
Lets insert the
date into the table :
|
Insert into
test_compound_trigger values (1,'John James');
|
Output
|
-------------------------------------------------
|
executed before statement trigger
|
value of i is 110
|
-------------------------------------------------
|
-------------------------------------------------
|
executed before each row trigger
|
value of i is 120
|
-------------------------------------------------
|
-------------------------------------------------
|
executed after each row trigger
|
value of i is 130
|
-------------------------------------------------
|
-------------------------------------------------
|
executed after statement trigger
|
value of i is 140
|
-------------------------------------------------
|
No comments:
Post a Comment