建站服务器

一  ORACLE触发器语法

 


http://www.zw1840.com/oracle/translation/concepts/html/22.Triggers.htm


http://www.zw1840.com/oracle/index.htm


 


     触发器是特定事件出现的时候,自动执行的代码块。类似于存储过程,触发器与存储过程的区别在于: 存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。


功能:


1、 允许/限制对表的修改


2、 自动生成派生列,比如自增字段


3、 强制数据一致性


4、 提供审计和日志记录


5、 防止无效的事务处理


6、 启用复杂的业务逻辑


 


触发器触发时间有两种:after和before。


1、触发器的语法:

CREATE [OR REPLACE] TIGGER 触发器名 触发时间 触发事件


ON 表名


[FOR EACH ROW]


BEGIN


     pl/sql 语句


END


其中:


触发器名:触发器对象的名称。


由于触发器是数据库自动执行的,因此该名称只是一个名称,没有实质的用途。


触发时间:指明触发器何时执行,该值可取:


before—表示在数据库动作之前触发器执行;


after—表示在数据库动作之后出发器执行。


触发事件:指明哪些数据库动作会触发此触发器:


insert:数据库插入会触发此触发器;


update:数据库修改会触发此触发器;


delete:数据库删除会触发此触发器。


表 名:数据库触发器所在的表。


for each row:对表的每一行触发器执行一次。如果没有这一选项,则只对整个表执行一次。


2、举例:

下面的触发器在更新表auths之前触发,目的是不允许在周末修改表:


create trigger auth_secure before insert or update or delete //对整表更新前触发


on auths


begin


if(to_char(sysdate,\’DY\’)=\’SUN\’


RAISE_APPLICATION_ERROR(-20600,\’不能在周末修改表auths\’);


end if;


end


 


例子:


CREATE OR REPLACE TRIGGER CRM.T_SUB_USERINFO_AUR_NAME AFTER UPDATE OF STAFF_NAME


ON CRM.T_SUB_USERINFO


REFERENCING OLD AS OLD NEW AS NEW


FOR EACH ROW


declare


begin


  if :NEW.STAFF_NAME!=:OLD.STAFF_NAME then


   begin


     –客户投诉


    update T_COMPLAINT_MANAGE set SERVE_NAME=:NEW.STAFF_NAME where SERVE_SEED=:OLD.SEED;


    –客户关怀


update T_CUSTOMER_CARE set EXECUTOR_NAME=:NEW.STAFF_NAME


where EXECUTOR_SEED=:OLD.SEED;


    –客户服务


update T_CUSTOMER_SERVICE set EXECUTOR_NAME=:NEW.STAFF_NAME


where EXECUTOR_SEED=:OLD.SEED;


    end;


  end if;


end T_sub_userinfo_aur_name;


/


二  Oracle触发器详解  

开始:


create trigger biufer_employees_department_id


before insert or update of department_id on employees


referencing old as old_value new as new_value


for each row


when (new_value.department_id<>80 )


begin


:new_value.commission_pct :=0;


end;


/


   1、触发器的组成部分:

1、 触发器名称


2、 触发语句


3、 触发器限制


4、 触发操作


    1.1、触发器名称

create trigger biufer_employees_department_id


 


命名习惯:


biufer(before insert update for each row)


employees 表名


department_id 列名


    1.2、触发语句

比如:


表或视图上的DML语句


DDL语句


数据库关闭或启动,startup shutdown 等等


before insert or update


       of department_id


       on employees


referencing old as old_value


            new as new_value


for each row


说明:


1、 无论是否规定了department_id ,对employees表进行insert的时候


2、 对employees表的department_id列进行update的时候


    1.3、触发器限制

when (new_value.department_id<>80 )


限制不是必须的。此例表示如果列department_id不等于80的时候,触发器就会执行。


其中的new_value是代表更新之后的值。


    1.4、触发操作

是触发器的主体


begin


       :new_value.commission_pct :=0;


end;


主体很简单,就是将更新后的commission_pct列置为0


 


触发:


insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,


department_id,salary,commission_pct )


values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);


select commission_pct from employees where employee_id=12345;


触发器不会通知用户,便改变了用户的输入值。


2、触发器的类型有:

触发器类型:


1、 语句触发器


2、 行触发器


3、 INSTEAD OF 触发


4、 系统条件触发器


5、 用户事件触发器


2.1、语句级触发器.(语句级触发器对每个DML语句执行一次)

       是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次。比如,无论update多少行,也只会调用一次update语句触发器。


实例:


create or replace trigger tri_test


after insert or update or delete on test


begin


if updating then


dbms_output.put_line(\’修改\’);


elsif deleting then


dbms_output.put_line(\’删除\’);


elsif inserting then


dbms_output.put_line(\’插入\’);


end if;


end;  


2.2、行级触发器.(行级触发器对DML语句影响的每个行执行一次)

实例一:


–触发器


–行级触发器


create table test(sid number,sname varchar2(20));–创建一个表


create sequence seq_test;–创建序列


 


create or replace trigger tri_test–创建触发器


before insert or update of sid on test


for each row–触发每一行


begin


if inserting then


select seq_test.nextval into:new.sid from dual;


else


raise_application_error(-20020,\’不允许更新ID值!\’);–中断程序


end if;


end;  


–测试,插入几条记录


insert into test values(0,\’ff\’);


insert into test values(0,\’ff\’);


insert into test values(0,\’tt\’);


输出结果如下图所示:



 


实例二:


–创建一个触发器,无论用户插入新记录,还是修改emp表的job列,都将用户指定的job列的值转换成大写.


create or replace trigger trig_job


before insert or update of job


on emp


for each row


<SPAN lang=EN-US style=FONT-FAMILY: 宋体; mso-bidi-font-size: 10.5

ORACLE 触发器语法及实例 一-编程知识网