oracle创建触发器的作用,Oracle 11g 触发器新增功能

Oracle

11g触发器新增功能：

触发器的执行顺序

组合触发器

启用和禁用触发器

触发器的执行顺序

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 ;

/

下面创建一个测试表和一个组合触发器触发的每个计时点与插入、更新和删除语句。触发操作都会被记录在PL/SQL的全局声明部分。最后输出pl/sql表的每条语句来显示该变量在整个声明中一直保持。

CREATE TABLE compound_trigger_test (

id           NUMBER,

description  VARCHAR2(50)

);

CREATE OR REPLACE TRIGGER

compound_trigger_test_trg

FOR

INSERT OR UPDATE OR DELETE ON compound_trigger_test

COMPOUND TRIGGER

--

Global declaration.

TYPE t_tab IS TABLE OF VARCHAR2(50);

l_tab t_tab := t_tab();

BEFORE STATEMENT IS

BEGIN

l_tab.extend;

CASE

WHEN INSERTING THEN

l_tab(l_tab.last) := 'BEFORE STATEMENT - INSERT';

WHEN UPDATING THEN

l_tab(l_tab.last) := 'BEFORE STATEMENT - UPDATE';

WHEN DELETING THEN

l_tab(l_tab.last) := 'BEFORE STATEMENT - DELETE';

END CASE;

END

BEFORE STATEMENT;

BEFORE EACH ROW IS

BEGIN

l_tab.extend;

CASE

WHEN INSERTING THEN

l_tab(l_tab.last) := 'BEFORE EACH ROW - INSERT (new.id=' || :new.id ||

')';

WHEN UPDATING THEN

l_tab(l_tab.last) := 'BEFORE EACH ROW - UPDATE (new.id=' || :new.id || '

old.id=' || :old.id || ')';

WHEN DELETING THEN

l_tab(l_tab.last) := 'BEFORE EACH ROW - DELETE (old.id=' || :old.id ||

')';

END CASE;

END

BEFORE EACH ROW;

AFTER EACH ROW IS

BEGIN

l_tab.extend;

CASE

WHEN INSERTING THEN

l_tab(l_tab.last) := 'AFTER EACH ROW - INSERT (new.id=' || :new.id ||

')';

WHEN UPDATING THEN

l_tab(l_tab.last) := 'AFTER EACH ROW - UPDATE (new.id=' || :new.id || '

old.id=' || :old.id || ')';

WHEN DELETING THEN

l_tab(l_tab.last) := 'AFTER EACH ROW - DELETE (old.id=' || :old.id ||

')';

END CASE;

END

AFTER EACH ROW;

AFTER STATEMENT IS

BEGIN

l_tab.extend;

CASE

WHEN INSERTING THEN

l_tab(l_tab.last) := 'AFTER STATEMENT - INSERT';

WHEN UPDATING THEN

l_tab(l_tab.last) := 'AFTER STATEMENT - UPDATE';

WHEN DELETING THEN

l_tab(l_tab.last) := 'AFTER STATEMENT - DELETE';

END CASE;

FOR i IN l_tab.first .. l_tab.last LOOP

DBMS_OUTPUT.put_line(l_tab(i));

END LOOP;

l_tab.delete;

END

AFTER STATEMENT;

END compound_trigger_test_trg;

通过对测试表的插入、更新和删除操作，组合触发器正像我们希望的那样。

SQL> SET SERVEROUTPUT ON

SQL> INSERT INTO compound_trigger_test

VALUES (1, 'ONE');

BEFORE STATEMENT - INSERT

BEFORE EACH ROW - INSERT (new.id=1)

AFTER EACH ROW - INSERT (new.id=1)

AFTER STATEMENT - INSERT

1 row created.

SQL> INSERT INTO compound_trigger_test

VALUES (2, 'TWO');

BEFORE STATEMENT - INSERT

BEFORE EACH ROW - INSERT (new.id=2)

AFTER EACH ROW - INSERT (new.id=2)

AFTER STATEMENT - INSERT

1 row created.

SQL> UPDATE compound_trigger_test SET id

= id;

BEFORE STATEMENT - UPDATE

BEFORE EACH ROW - UPDATE (new.id=2

old.id=2)

AFTER EACH ROW - UPDATE (new.id=2 old.id=2)

BEFORE EACH ROW - UPDATE (new.id=1

old.id=1)

AFTER EACH ROW - UPDATE (new.id=1 old.id=1)

AFTER STATEMENT - UPDATE

2 rows updated.

SQL> DELETE FROM compound_trigger_test;

BEFORE STATEMENT - DELETE

BEFORE EACH ROW - DELETE (old.id=2)

AFTER EACH ROW - DELETE (old.id=2)

BEFORE EACH ROW - DELETE (old.id=1)

AFTER EACH ROW - DELETE (old.id=1)

AFTER STATEMENT - DELETE

2 rows deleted.

SQL>

启用和禁用触发器

我们通常使用下面的命令禁用或启用触发器：

ALTER  TRIGGER  DISABLE;

ALTER  TRIGGER  ENABLE;

ALTER  TABLE    DISABLE  ALL  TRIGGERS;

ALTER  TABLE    ENABLE  ALL  TRIGGERS;

11g之前，触发器只能创建在启用状态，想要禁用，得创建之后在禁用触发器。但11g可以显示的指定触发器的启用或者禁用状态，默认是启用的。

CREATE TABLE trigger_control_test (

id          NUMBER,

description VARCHAR2(50)

);

CREATE OR REPLACE TRIGGER trigger_control_test_trg

BEFORE INSERT ON trigger_control_test

FOR EACH ROW

ENABLE

BEGIN

DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG - Executed');

END;

/

SQL> SET SERVEROUTPUT ON

SQL> INSERT INTO trigger_control_test

VALUES (1, 'ONE');

TRIGGER_CONTROL_TEST_TRG - Executed

1 row created.

SQL>

CREATE OR REPLACE TRIGGER

trigger_control_test_trg

BEFORE INSERT ON trigger_control_test

FOR EACH ROW

DISABLE

BEGIN

DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG - Executed');

END;

/

SQL> INSERT INTO trigger_control_test

VALUES (2, 'TWO');

1 row created.

详情见官方文档：