触发器的执行顺序
组合触发器
启用和禁用触发器
触发器的执行顺序
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
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
/