oracle触发器学习

触发器

什么是触发器

1.  触发器是在事件发生时隐式地自动运行的PL/SQL程序块，不能接收参数，不能被调用。

2.   触发器必须由事件才能触发。触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。

触发器的分类

DML触发器

DML触发器是定义在表上的触发器，由DML事件引发。

编写DML触发器的要素是：

l   确定触发的表，即在其上定义触发器的表。

l   确定触发的事件，DML触发器的触发事件有INSERT、UPDATE和DELETE三种。

l   确定触发时间。触发的时间有BEFORE和AFTER两种，分别表示触发动作发生在DML语句执行之前和语句执行之后。

l   确定触发级别，有语句级触发器和行级触发器两种。语句级触发器表示SQL语句只触发一次触发器，行级触发器表示SQL语句影响的每一行都要触发一次。

 

DML触发器的执行顺序：

由于在同一个表上可以定义多个DML触发器，因此触发器本身和引发触发器的SQL语句在执行的顺序上有先后的关系。它们的顺序是：

* 如果存在语句级BEFORE触发器，则先执行一次语句级BEFORE触发器。

* 在SQL语句的执行过程中，如果存在行级BEFORE触发器，则SQL语句在对每一行操作之前，都要先执行一次行级BEFORE触发器，然后才对行进行操作。如果存在行级AFTER触发器，则SQL语句在对每一行操作之后，都要再执行一次行级AFTER触发器。

* 如果存在语句级AFTER触发器，则在SQL语句执行完毕后，要最后执行一次语句级AFTER触发器。

 

DML触发器还有一些具体的问题，说明如下：

* 如果有多个触发器被定义成为相同时间、相同事件触发，且最后定义的触发器是有效的，则最后定义的触发器被触发，其他触发器不执行。

* 一个触发器可由多个不同的DML操作触发。在触发器中，可用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。这些谓词可以在IF分支条件语句中作为判断条件来使用。

* 在行级触发器中，用:new 和:old(称为伪记录)来访问数据变更前后的值。但要注意，INSERT语句插入一条新记录，所以没有:old记录，而DELETE语句删除掉一条已经存在的记录，所以没有:new记录。UPDATE语句既有:old记录，也有:new记录，分别代表修改前后的记录。引用具体的某一列的值的方法是： :old.字段名或:new.字段名

* 触发器体内禁止使用COMMIT、ROLLBACK、SAVEPOINT语句，也禁止直接或间接地调用含有上述语句的存储过程。

创建DML触发器

语法

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

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

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

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

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

l   当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

练习一、利用触发器，记录所有对Grade表进行的添加，修改，删除历史。

建立历史表，用于记录历史

create table grade_his

(gradeid_before number(6), gradename_beforevarchar2(20),

gradeid_after number(6), gradename_aftervarchar2(20),

operated_type varchar2(20),operated_byvarchar2(20), operated_datedate

)

;

建立触发器，用于记录插入历史

create orreplace trigger trg_insert_grade

after inserton grade

for eachrow

declare

begin

  insert into grade_his(gradeid_after, gradename_after, operated_type,operated_by, operated_date)

  values

  (:new.gradeid,:new.gradename,'插入',sys_context('userenv','SESSION_USER'),sysdate);

End;

建立触发器，用来记录修改历史

create orreplace trigger trg_update_grade

before updateon grade

for eachrow

declare

begin

  insert into grade_his(gradeid_before, gradename_before, gradeid_after, gradename_after, operated_type,operated_by, operated_date)

  values

  (:old.gradeid,:old.gradename,:new.gradeid,:new.gradename,'更新',sys_context('userenv','SESSION_USER'),sysdate);

end;

建立触发器，用来记录删除历史

create orreplace trigger trg_delete_grade

before deleteon grade

for eachrow

declare

begin

  insert into grade_his(gradeid_before, gradename_before,  operated_type,operated_by, operated_date)

  values

  (:old.gradeid,:old.gradename,'删除',sys_context('userenv','SESSION_USER'),sysdate);

end;

一个例子：

要求周六周日不能删除数据

begin
  
if to_char(sysdate,'d')in(6,7) then
  raise_application_error(-20001,'不能删除');--第一次吧application忘了
end if;
end;

测试：

delete from dept where dno = 1001;

另一个自己的例子：

   create or replace trigger trig_trage after insert on tradeinfo
     for each row
     begin
     if :new.transtype='存入' then
      update cardinfo set balance= :new.transmoney+balance where cardid= :new.cardid;--注意表
     else
      update cardinfo set balance = balance-:new.transmoney where cardid = :new.cardid;
     end if;
     end;



     测试：
     insert into tradeinfo values(to_date('2016-02-03','YYYY-MM-DD HH24:mi:ss'),'1001200130014001','支取',100);

触发器多用于关联表的更新，记录等方面。