Oracle触发器2-DML触发器

  DML触发器是最常见的触发器类型，开发人员用的比较多；而其他类型的触发器主要是用于数据库管理或者审计，DBA用的比较多。

1、DML触发器简介：

BEFORE 触发器

这种触发器是在某个操作发生之前触发的，比如before insert就是在插入操作之前触发。

AFTER 触发器

这种触发器是在某个操作发生之后触发的，比如after update就是在插入操作之前触发。

语句级别触发器

这种触发器是由整个SQL语句触发的。这个SQL语句可能操作数据库表的一条或者多条数据。

行级别触发器

这种触发器针对的是SQL语句执行过程中操作的每一行记录。假设books表中有1000行记录。下面的update语句就会修改1000行记录：

update books set title = upper(title);

如果我在books表上定义了一个行级别的更新触发器，这个触发器就会被触发1000次。

伪记录 NEW

这是一个被叫做NEW的数据结构，看起来和PL/SQL中的记录非常相似。只有在更新操作和插入操作的DML触发器中才能使用这个伪记录；这个记录包含的是被操作的行修改之后的值。

伪记录 OLD

这是一个被叫做OLD的数据结构，看起来和PL/SQL中的记录非常相似。只有在更新操作和插入操作的DML触发器中才能使用这个伪记录；这个记录包含的是被操作的行修改之前的值。

WHEN 子句

DML触发器用这个子句来确定是否应该执行触发器的代码，我们可以用它来避免不必要的执行。

有关事务

DML触发器会参与到触发他们的事务中。

如果触发器抛出了异常，这部分事务会回滚（rollback）。

如果触发器本身也执行了DML语句（比如向日志表中插入一行数据），这个DML同时也会成为主体事务的一部分。

不能在DML触发器里执行commit或者rollback语句。

2、创建DML触发器

1 CREATE [OR REPLACE] TRIGGER trigger_name   --指定一个触发器名字， or replace 可选
2 {BEFORE | AFTER}     --指定触发器时机是在语句执行之前或者之后。
3 {INSERT | DELETE | UPDATE | UPDATE OF column_list } ON table_name --指定触发器应用的DML类型组合：插入、更新或者删除操作。
4 [FOR EACH ROW]   --如果指定了for each row 则语句处理的每一行记录都会激活触发器。
5 [WHEN (...)]            --通过这个可选的when子句，可以避免不必要的执行
6 [DECLARE ... ]  
7 BEGIN
8 ...executable statements...    --执行体
9 [EXCEPTION ... ]     --可选异常处理部分
10 END [trigger_name];    

Examples:

-- an after statement level trigger

CREATE OR REPLACE TRIGGER statement_trigger
AFTER INSERT ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Insert Statement Level');
END;
/
/*-- an after row level trigger */
CREATE OR REPLACE TRIGGER row_trigger
AFTER INSERT ON to_table
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Insert Row Level');
END;
/

-- a before statement level trigger
CREATE OR REPLACE TRIGGER before_statement_trigger
BEFORE INSERT ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before Insert Statement Level');
END;
/

-- a before row level trigger
CREATE OR REPLACE TRIGGER before_row_trigger
BEFORE INSERT ON to_table
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before Insert Row Level');
END;
/

-- after insert statement
CREATE OR REPLACE TRIGGER after_insert_statement
AFTER INSERT ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Insert Statement');
END;
/

-- after update statement
CREATE OR REPLACE TRIGGER after_update_statement
AFTER UPDATE ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Update Statement');
END;
/

-- after delete statement
CREATE OR REPLACE TRIGGER after_delete_statement
AFTER DELETE ON to_table
BEGIN
  DBMS_OUTPUT.PUT_LINE('After Delete Statement');
END;
/

2.1、使用WHEN子句

例如使用WHEN子句确保只有把薪水修改成不同的值时触发器代码才会执行：

CREATE OR REPLACE TRIGGER check_raise
   AFTER UPDATE OF salary
   ON employees
   FOR EACH ROW
   WHEN (   (old.salary != new.salary)
         OR (old.salary IS NULL AND new.salary IS NOT NULL)
         OR (old.salary IS NOT NULL AND new.salary IS NULL))
BEGIN
   NULL;
END;
/

WHEN子句使用注意事项：

a.要把整个判断逻辑表达式括起来（）

b.不要在OLD和NEW之前加上”:”

c.使用WHEN子句时只能使用SQL内置函数；

2.2、使用NEW和OLD伪记录

CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
   BEFORE INSERT
   ON ceo_compensation
   FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO ceo_comp_history
       VALUES (
                  :new.name
                , :old.compensation
                , :new.compensation
                , 'AFTER INSERT'
                , SYSDATE
              );

   COMMIT;
END;
/