Oracle数据库之PL/SQL触发器

 1 plsql_trigger_source ::========
 2 
 3 [schema.] trigger_name
 4   { simple_dml_trigger
 5   | instead_of_dml_trigger
 6   | compound_dml_trigger
 7   | system_trigger
 8   }
 9 
10 simple_dml_trigger ::========
11 
12 { BEFORE | AFTER } dml_event_clause [ referencing_clause ] [ FOR EACH ROW ]
13   [ trigger_edition_clause ] [ trigger_ordering_clause ]
14     [ ENABLE | DISABLE ] [ WHEN ( condition ) ] trigger_body
15 
16 instead_of_dml_trigger ::=
17 
18 INSTEAD OF { DELETE | INSERT | UPDATE } [ OR { DELETE | INSERT | UPDATE } ]...
19 ON [ NESTED TABLE nested_table_column OF ] [ schema. ] noneditioning_view
20 [ referencing_clause ] [ FOR EACH ROW ]
21 [ trigger_edition_clause ] [ trigger_ordering_clause ]
22 [ ENABLE | DISABLE ] trigger_body
23 
24 system_trigger ::=======
25 
26 { BEFORE | AFTER | INSTEAD OF }
27 { ddl_event [OR ddl_event]...
28 | database_event [OR database_event]...
29 }
30 ON { [schema.] SCHEMA
31    | DATABASE
32    }
33 [ trigger_ordering clause ]
34 
35 dml_event_clause ::============
36 
37 { DELETE | INSERT | UPDATE [ OF column [, column ]... ] }
38 [ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }...
39 ON [ schema.] { table | view }
40 
41 referencing_clause ::==========
42 
43 REFERENCING
44  { OLD [ AS ] old
45  | NEW [ AS ] new
46  | PARENT [ AS ] parent
47  }...
48 
49 trigger_body ::=======
50 
51 { plsql_block | CALL routine_clause }

说明：

BEFORE和AFTER指出触发器的触发时间分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

NEW只在UPDATE、INSERT的DML触发器内可用，它包含了修改发生后被影响行的值。

OLD只在UPDATE、DELETE的DML触发器内可用，它包含了修改发生前被影响行的值。

FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE和AFTER触发器为语句触发器，而INSTEAD OF触发器则只能为行触发器。

WHEN子句说明触发约束条件。Condition为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL函数。WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中，不能用在INSTEAD OF行触发器和其它类型的触发器中。

INSTEAD OF选项（创建替代触发器）使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF触发器。

ddl_event：一个或多个DDL事件，事件间用OR分开。

database_event：一个或多个数据库事件，事件间用OR分开。

示例1，在插入数据时，自动使用序列编号：

1 CREATE OR REPLACE TRIGGER EMP_INSERT_ID
2 BEFORE INSERT ON employee FOR EACH ROW
3 BEGIN
4    SELECT SEQ_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
5 END;

 1 示例2，在多表联接的视图中插入数据：
 2 
 3 -- 创建视图
 4 CREATE OR REPLACE VIEW vw_emp AS
 5 SELECT e.name ename, e.address, d.name dname
 6 FROM employee e, dept d
 7 WHERE e.did = d.id;
 8 
 9 -- 创建触发器
10 CREATE TRIGGER emp_insert_trigger
11    INSTEAD OF INSERT ON vw_emp
12 DECLARE
13    v_did dept.id%TYPE;
14 BEGIN
15    SELECT id INTO v_did FROM dept WHERE name = :NEW.dname;
16    INSERT INTO emp (name, address, did) VALUES (:NEW.ename, :NEW.address, v_did);
17 END emp_insert_trigger;

 1 示例3，创建实例启动触发器：
 2 
 3 -- 创建记录操作事件的表
 4 CREATE TABLE event_table(
 5    event VARCHAR2(50),
 6    time DATE
 7 );
 8 
 9 -- 创建触发器
10 CREATE OR REPLACE TRIGGER tr_startup
11    AFTER STARTUP
12    ON DATABASE
13 BEGIN
14    INSERT INTO event_table(event, time)
15     VALUES(ora_sysevent, SYSDATE);
16 END;

 DML触发器

DML触发器对我们开发人员来说是最常用的。DML触发器是由数据库的INSERT、UPDATE、DELETE操作触发，该类触发器可以在上述语句之前或之后执行，也可以每个受影响的行执行一次。

条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的条件谓词：

1. INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
2. UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE时，如果修改了column_x列，则取值为TRUE，否则为FALSE。
3. DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。

示例：

 1 CREATE OR REPLACE TRIGGER emp_sal_trigger
 2    BEFORE UPDATE OF salary OR DELETE
 3    ON employee FOR EACH ROW
 4    WHEN (old.did = 1)
 5 BEGIN
 6   CASE
 7      WHEN UPDATING (‘salary‘) THEN
 8         IF :NEW.salary < :old.salary THEN
 9            RAISE_APPLICATION_ERROR(-20001, ‘部门1的员工工资不能降‘);
10         END IF;
11      WHEN DELETING THEN
12           RAISE_APPLICATION_ERROR(-20002, ‘不能删除部门1的员工记录‘);
13   END CASE;
14 END emp_sal_trigger;

5. 替代触发器

INSTEAD OF用于对视图的DML触发，由于视图有可能是由多个表联结(JOIN)而成，因而并非所有的视图都是可更新的，但可以按照所需的方式执行更新。

创建INSTEAD OF触发器需要注意以下几点：

1. 只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
2. 不能指定BEFORE或AFTER选项。
3. FOR EACH ROW子句是可选的。
4. 没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。

示例：

1 CREATE OR REPLACE TRIGGER emp_delete_trigger
2    INSTEAD OF DELETE ON vw_emp FOR EACH ROW
3 DECLARE
4    v_did dept.id%TYPE;
5 BEGIN
6    SELEC id INTO v_did FROM dept WHERE name=:OLD.dname;
7    DELETE FROM employee WHERE did= v_did;
8 END emp_delete_trigger;

6. 系统触发器

系统触发器可以在DDL或数据库系统上被触发，数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。

系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器，默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。

系统触发器的种类和事件出现的时机：

 1 -- 创建记录用户登录注销日志的表
 2 CREATE TABLE log_on_off_log
 3 (user_name VARCHAR2(20),
 4  logon_date timestamp,
 5  logoff_date timestamp);
 6 
 7 -- 创建登录触发器
 8 CREATE OR REPLACE TRIGGER logon_trigger
 9    AFTER LOGON ON DATABASE
10 BEGIN
11    INSERT INTO log_on_off_log (user_name, logon_date) VALUES (ora_login_user, systimestamp);
12 END logon_trigger;
13 
14 -- 创建退出触发器
15 CREATE OR REPLACE TRIGGER logoff_trigger
16    BEFORE LOGOFF ON DATABASE
17 BEGIN
18    INSERT INTO log_on_off_log (user_name, logoff_date) VALUES (ora_login_user, systimestamp);
19 END logoff_trigger;

Oracle数据库之PL/SQL触发器

标签：