接上一章，这次将会介绍

• PL/SQL触发器

23.PL/SQL触发器

存储过程和存储函数是完成一个特定功能的PLSQL程序，触发器也是。它与存储过程和函数不同的是，触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的，即触发器是当某个事件发生时自动地隐式运行。
  触发器的作用就是可以实现一般的约束无法完成的复杂约束，从而实现更为复杂的完整性要求。
  oracle触发器在以下任一事件发生时将被执行：

• 数据库操作(DML)语句(DELETE，INSERT，UPDATE或)
• 数据库定义(DDL)语句(CREATE，ALTER或DROP)
• 数据库操作(SERVERERROR，登录，注销，启动或关机)

触发器具体应用场景
1.复杂的安全性的场景(涉及到权限的问题)；
2.数据的确认(涉及数据是否合理问题)；
3.数据的审计(跟踪表的数据操作，涉及到数据的增、删、改的操作记录)；
4.数据的备份和同步(备份和同步重要);

例子：
1.下班时间不能插入数据库；
2.涨工资越涨越高，而不应该越涨越少；
3.把操作数据的时间和帐户等信息记录下来；
4.不同的数据表间进行同步备份

触发器的三种类型
（1）DML触发器由DML语句触发，语句的类型确定DML触发器的类型，可以定义为INSERT,UPDATE,DELETE操作，可以在操作之前或之后被触发。
（2）INSTEAD OF触发器用来代替通常的触发动作，即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确，如正确才进行相应的操作。INSTEAD OF触发器只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。它是用来替换所使用实际语句的触发器，只能在行级上触发。
（3）系统触发器在DDL事件(CREATE 、ALTER及DROP 等)或者数据库事件（比如用户登陆注销，服务器错误等等）上触发。

行级触发器和语句触发器

• 语句触发器（针对的是表）
• 语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器，不管这条语句执行了多少行。
• 行级触发器（针对的是行）
• 对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器。
• 在行级触发器中使用:OLD和:NEW伪记录变量，识别值的状态。
• :NEW 修饰符访问操作完成后列的值
• :OLD 修饰符访问操作完成前列的值

触发时间
BEFORE：事前触发器运行于触发事件发生之前，通常事前触发器可以获取事件之前和新的字段值。
AFTER：事后触发器运行于触发事件发生之后。

使用触发器谓词
ORACLE 提供三个参数INSERTING, UPDATING,DELETING来判断触发了哪些操作。
谓词      行为
INSERTING  如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE
UPDATING   如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE
DELETING   如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE

创建触发器

CREATE [OR REPLACE ] TRIGGER tigger_name
{BEFORE | AFTER | INSTEAD OF } -- 触发时间
{INSERT [OR] | UPDATE [OR] | DELETE}  -- 触发事件
[OF col_name] -- 指定将被更新的列名
ON [schema.]table_name | [schema.]view_name  -- 指定触发器相关联的表/视图
[REFERENCING OLD AS o NEW AS n] -- 可以参考新旧值的各种DML语句
[FOR EACH ROW] -- 指定的行级触发器，即触发器将每一行受到影响执行
WHEN (condition)  -- 触发器将触发的条件。此子句仅适用于行级触发器有效
DECLARE --触发操作
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

• FOR EACH ROW选项说明触发器为行触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。
• REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。
• WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

编写触发器时，需要注意以下几点
l  触发器不接受参数。
l  一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
l  在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。
l  触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。
l  在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。
l  触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。
l  在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。
l  在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。
l  不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

DML触发器示例
(1)建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE OR REPLACE TRIGGER tr_del_emp 
   BEFORE DELETE --指定触发时机为删除操作前触发
   ON scott.emp 
   FOR EACH ROW   --说明创建的是行级触发器 
BEGIN
   --只会记录被删除职工的记录，因为只有被删除职工才有:old
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;

(2)限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_time
   BEFORE INSERT OR DELETE OR UPDATE 
   ON departments
BEGIN
 --使用谓词进行约束
 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
     RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表');
 END IF;
END;

(3)限定只对部门号为80的记录进行行触发器操作。

CREATE OR REPLACE TRIGGER tr_emp_sal_comm
    BEFORE UPDATE OF salary, commission_pct OR DELETE
    ON HR.employees
    FOR EACH ROW
    WHEN (old.department_id = 80)
BEGIN
 CASE
     WHEN UPDATING ('salary') THEN
        IF :NEW.salary < :old.salary THEN
           RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');
        END IF;

     WHEN UPDATING ('commission_pct') THEN
        IF :NEW.commission_pct < :old.commission_pct THEN
           RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');
        END IF;

     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');
     END CASE;
END; 

(4)利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。

CREATE OR REPLACE TRIGGER tr_reg_cou
   AFTER update OF region_id
   ON regions
   FOR EACH ROW
BEGIN
 DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id
                  ||'、新的region_id值是'||:new.region_id);
 UPDATE countries SET region_id = :new.region_id
 WHERE region_id = :old.region_id;
END;

(5)在触发器中调用过程。

CREATE OR REPLACE PROCEDURE add_job_history
 ( p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
  , p_end_date        job_history.end_date%type
   )
AS
BEGIN
 INSERT INTO job_history (employee_id, start_date, end_date)
  VALUES(p_emp_id, p_start_date, p_end_date);
END add_job_history;

--创建触发器调用存储过程...
CREATE OR REPLACE TRIGGER update_job_history
 AFTER UPDATE OF job_id, department_id ON employees
 FOR EACH ROW
BEGIN
 add_job_history(:old.employee_id, :old.hire_date, sysdate);
END;

替代(INSTEAD OF)触发器
创建格式：

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

【示例】
(1)假设有一个叫emp_view的视图：

CREATE OR REPLACE VIEW emp_view AS 
SELECT deptno, count(*) total_employeer, sum(sal) total_salary 
FROM emp GROUP BY deptno;

如果我们直接在视图中删除表的属性是违法的：

DELETE FROM emp_view WHERE deptno=10;

但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：

CREATE OR REPLACE TRIGGER emp_view_delete
   INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
   DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete; 

系统事件触发器
创建格式：

CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE|AFTER} 
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.]SCHEMA }
[WHEN condition]
PL/SQL_block | CALL procedure_name;

• ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；
• database_event_list：一个或多个数据库事件，事件间用 OR 分开；
• 系统事件触发器既可以建立在一个模式(SCHEMA)上，又可以建立在整个数据库(DATABASE)上。

下面给出系统触发器的事件种类和事件出现的时机（前或后）：

在触发器中，可以通过使用系统事件函数（Event Attribute Functions）来获取一些信息。以下是系统事件函数的详细情况：

ORACLE 8I开始，提供了一个新的函数“SYS_CONTEXT”。通过使SYS_CONTEXT函数可以获得一些和用户相关的信息，比如：
SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM DUAL;

• SYS_CONTEXT函数使用格式：sys_context(‘namespace’,’parameter’{,length})
• sys_context函数最常用的就是userenv命名空间(namespace)下的系列参数
• parameter(用户环境)的取值包括：
  SESSIONID： SESSION的ID
  SESSION_USER：session所属的用户名
  SESSION_USERID：当前SESSION所属的用户id
  DB_DOMAIN：当前数据库的DOMAIN
  DB_NAME：当前数据库的名称
  HOST：客户端的主机名称
  OS_USER：客户端的操作系统用户名
  IP_ADDRESS：客户端的IP地址
  ....(省略)

【示例】
(1)创建登录、退出触发器。

CREATE TABLE log_event
(user_name VARCHAR2(10),
 address VARCHAR2(20), 
 logon_date timestamp,
 logoff_date timestamp); 

--创建登录触发器
CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logon_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logon;
--创建退出触发器
CREATE OR REPLACE TRIGGER tr_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logoff_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logoff;

(2)创建触发器，存放有关事件信息。

CREATE TABLE ddl_event
(crt_date timestamp PRIMARY KEY,
 event_name VARCHAR2(20), 
 user_name VARCHAR2(10),
 obj_type VARCHAR2(20),
 obj_name VARCHAR2(20));

--创建触发器
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON SCHEMA
BEGIN
   INSERT INTO ddl_event VALUES
   (systimestamp,ora_sysevent, ora_login_user, 
    ora_dict_obj_type, ora_dict_obj_name);
END tr_ddl;

(3)禁止用户做DDL操作

CREATE OR REPLACE TRIGGER ddl_fb 
BEFORE  ALTER OR DROP  OR RENAME
ON SHCEMA 
BEGIN
  Raise_application_error(-20030,'此系统已经运行，不允许对表进行DDL维护');
end;

(4)错误跟踪的触发器

CREATE OR REPLACE TRIGGER log_errors 
AFTER SERVERERROR ON DATABASE
BEGIN
 IF (IS_SERVERERROR (1017)) THEN //1017:错误码
  insert into log_audit (login_date,USERNAME,USER_IP,error_code) values(sysdate,ora_login_user,ora_client_ip_address,'ORA-1017');
 ELSIF (IS_SERVERERROR (2449)) THEN
  insert into log_audit (login_date,USERNAME,USER_IP,error_code) values(sysdate,ora_login_user,ora_client_ip_address,'ORA-2449');
 END IF;
END;

(5)创建个触发器限制scott用户从指定IP登录

CREATE OR REPLACE TRIGGER restrict_login
AFTER LOGON ON DATABASE
DECLARE
 RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
 ALLOWED_IP      VARCHAR2(16) := '10.10.10.10';
 LOGON_USER      VARCHAR2(32);
 CLIENT_IP       VARCHAR2(16);
BEGIN
 LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');
 CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
  IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
   RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
 END IF;
END;

重新编译触发器
  如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。
  在PL/SQL程序中可以调用ALTER TRIGGER语句重新编译已经创建的触发器，格式为：

ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]

• DEBUG 选项要求编译器生成PL/SQL 程序条使其所使用的调试代码。