触发器的执行顺序
组合触发器
启用和禁用触发器
触发器的执行顺序
Oracle允许超过一个触发器建立相同的时间点，但它从来不保证它们的执行顺序。Oracle 11g触发器语法现在包括FOLLOWS子句保证了触发器的执行顺序在相同时间点。下面的例子创建一张表和两个在相同时间点触发的触发器
CREATE TABLE trigger_follows_test (
id NUMBER,
description VARCHAR2(50)
);
CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_1 – Executed’);
END;
/
CREATE OR REPLACE TRIGGER trigger_follows_test_trg_2
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_2 – Executed’);
END;
/
我们插入测试表，触发器的执行顺序没有保证。
SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (1, ‘ONE’);
TRIGGER_FOLLOWS_TEST_TRG_1 – Executed
TRIGGER_FOLLOWS_TEST_TRG_2 – Executed
1 row created.
SQL>
我们可以指定触发器TRIGGER_FOLLOWS_TEST_TRG_2在TRIGGER_FOLLOWS_TEST_TRG_1之前执行通过重建使用FOLLOWS子句的TRIGGER_FOLLOWS_TEST_TRG_1得触发器。
CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
FOLLOWS trigger_follows_test_trg_2
BEGIN
DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_1 – Executed’);
END;
/
现在TRIGGER_FOLLOWS_TEST_TRG_1总是TRIGGER_FOLLOWS_TEST_TRG_2后面执行
SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (2, ‘TWO’);
TRIGGER_FOLLOWS_TEST_TRG_2 – Executed
TRIGGER_FOLLOWS_TEST_TRG_1 – Executed
1 row created.
SQL>
删除测试表。
DROP TABLE trigger_follows_test;
组合触发器
组合触发器允许代码为一个或多个时间点为特定的对象结合起来，单独的时间点可以共享一个全局声明部分，这种状态在试用期间一直保持，一旦语句结束，不管执行成功还是错误，，触发器状态将被清理干净。在以前的版本中，该类型功能只能通过定义多个触发器和全局变量在一个单独的包中。
组合触发器的动作定义和其他DML触发器相同，此外增加了 COMPOUND TRIGGER 子句。触发器的主体是由一个可选的全局声明部分及一个或多个计时点部分，每个可能包含一个不能维持状态的本地声明。
CREATE OR REPLACE TRIGGER
FOR ON
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 ;
/