触发器

触发器的创建规则

触发器是与一个表或数据库时间联系在一起的，当特定事件出现时将自动执行触发器的代码块。触发器与过程的区别在于：过程是由用户或应用程序显示调用的，而触发器是不能被直接调用的。
触发器能够执行的功能包括：
⑴ 自动生成数据。
⑵ 强制复杂的完整性约束条件。
⑶ 自定义复杂的安全权限。
⑷ 提供审计和日志记录。
⑸ 启用复杂的业务逻辑。
Oracle支持的触发器有以下几种类型：
⑴ 行级触发器：对DML语句影响的每一行执行一次。
⑵ 语句级触发器：对每个DML语句执行一次。
⑶ INSTEAD OF 触发器：定义在视图上，而不是表上。用来替换所使用实际语句。
⑷ 系统事件触发器：在Oracle数据库系统的事件中进行触发，如Oracle的启动和关闭。
⑸ 用户事件触发器：是指与数据库定义语句DDL或用户的登录/注销等事件相关的触发器。如用户连接到数据库、修改表结构等。
创建触发器的语法规则如下：

CREATE [ OR REPLACE ] TRIGGER trigger_name
[ BEFORE | AFTER | INSTEAD OF ] trigger_event
{ ON table_name | view_name | DATABASE }
[ FOR EACH ROW ]
[ ENABLE | DISABLE ]
[ WHEN trigger_condition ]
[ DECLARE declaration_statements ; ]
BEGIN
   trigger_boby;
END [ trigger_name ];

也可简化如下：

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
 pl/sql语句
end

语法解释如下：
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取。
before：表示在数据库动作之前触发器执行。
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器。
insert：数据库插入会触发此触发器。
update：数据库修改会触发此触发器。
delete：数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。
示例：
下面的触发器代码在更新表tb_emp之前触发，目的是不允许在周末修改表

create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
  IF(to_char(sysdate,'DY')='星期日') THEN
    RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
  END IF;
END;

---

语句触发器

如果在创建触发器时未使用FOR EACH ROW子句，则创建的触发器为语句级触发器。语句级触发器在被触发后只执行一次，而不管这一操作会影响到数据库中多少行记录。

建立语句级触发器

⑴ 建立日志信息表EMP_LOG用来存储用户对表的操作。

CREATE TABLE emp_log(
    who VARCHAR2(30),
    WHEN DATE);

⑵ 创建语句级触发器，将用户对EMP表的操作记录到EMP_LOG表中。

CREATE OR REPLACE TRIGGER emo_op
before INSERT OR DELETE OR UPDATE
ON emp
BEGIN
   INSERT INTO emp_log(who,when)
   VALUES(user,sysdate);
END;

⑶ 更新EMP表，确认触发器是否能够正常运行。

UPDATE emp SET sal=sal*1.1;
SELECT # FROM emp_log

使用谓词触发器

若上述的emo_op触发器使用了多个触发事件，可以使用条件谓词确定是哪个语句导致了触发器的激活。
语法规则：

begin
   if inserting then
       --insert  语句触发
   elsif updating then
       --update 语句触发
   elsif deleting then
       --delete 语句触发
    end if;
end;

示例：
⑴ 修改EMP_LOG表，添加ACTION列，存储用户对表进行的操作。

ALTER TABLE emp_log ADD(action VARCHAR2(50));

⑵ 修改触发器以便记录语句的类型。

create or replace
TRIGGER emp_op
before INSERT OR DELETE OR UPDATE
ON emp
DECLARE
    var_action VARCHAR2(50);
BEGIN
    IF inserting  then
       var_action:='INSERT';
    IF updating then
       var_action:='UPDATE';
    IF deleting then
       var_action:='DELETE';
    END IF;
    INSERT INTO emo_log(who,when,action);
    VALUES(user,sysdate,var_action);
END;

⑶ 更新某雇员信息，测试触发器执行情况。

UPDATE emp SET sal=sal*1.2 WHERE empno=7369;
SELECT * FROM emo_log
DELETE FROM emp WHERE empno=8000;
SELECT * FROM emo_log

---

行级触发器

在创建触发器时，如果使用了FOR EACH ROW子句，则该触发器为行级触发器。当一个DML语句操作影响到了数据库中的多行数据时，行级触发器会针对于每一行执行一次。
行级触发器可以引用受到影响的行值。为了获取某列在DML操作前后的数据，Oracle提供了两种特殊的标识符——:OLD和:NEW。
⑴ :OLD.column_name 获取某列的旧数据，在INSERT中不能使用。
⑵ :NEW.column_name 获取某列的新数据，在DELETE中不能使用。

示例：

⑴ 创建测试表FOO以及随同使用的序列，序列的作用是自动生成一组排序数。

CREATE TABLE foo(sid NUMBER,sname VARCHAR(20));
CREATE sequence seq_foo;

⑵ 创建生成主键的行级触发器。

CREATE OR REPLACE
TRIGGER FOO_TRIGGER
   BEFORE INSERT OR UPDATE ON FOO
   FOR EACH ROW
BEGIN
    IF inserting THEN
       SELECT seq_foo.nextval INTO :new.sid
       FROM dual;
    ELSE
       raise_application_error(-20020,'不允许更新ID值！');  --把异常信息返回给调用的客户端
    END IF; 
END;

⑶ 向表FOO添加两行数据，测试触发器是否成功执行。

insert into foo(sname) values('张三');
insert into foo(sname) values('李四');