Oracle 触发器语法

一 Oracle触发器语法

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

　　功能：

　　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 triggerauth_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 triggerbiufer_employees_department_id
　　beforeinsertorupdateofdepartment_idonemployees
　　referencingoldasold_value newasnew_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, ‘[e-mail]’,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;

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程。本文继续介绍Oracle 触发器语法及实例。

 

• 　　测试,插入几条记录　　

      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
　　begin
　　if inserting then
　　:new.job:=upper(:new.job);
　　else
　　:new.job:=upper(:new.job);
　　end if;
　　end;

　　2.3、instead of触发器.

　　(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.)

　　语法如下:

   　create or replace triggertrig_test
　　instead ofinsert or update on表名
　　referencing new as n
　　for each row
　　declare
　　..........
　　begin
　　........
　　end;

　　2.4、模式触发器.

　　可以在模式级的操作上建立触发器.

　　实例如下:　

      create or replace trigger log_drop_obj
　　after drop on schema
　　begin
　　insert into .....
　　end;

　

　2.5、数据库级触发器.

　　可以创建在数据库事件上的触发器,包括关闭,启动,服务器错误,登录等.这些事件都是实例范围的,不与特定的表或视图关联.

　　实例:　　

      create or replace trigger trig_name
　　after startup on database
　　begin
　　...........
　　end;

　　2.6、例子：

　　需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。　

      Create table foo(a number);
　　Create trigger biud_foo
　　Before insert or update or delete
　　On foo
　　Begin
　　If user not in (‘DONNY’) then
　　Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
　　End if;
　　End;
　　/

　　即使SYS，SYSTEM用户也不能修改foo表

　　2.7、[试验]

　　对修改表的时间、人物进行日志记录。

　　1、 建立试验表　

   　create table employees_copy as select *from hr.employees

　　2、 建立日志表

      create table employees_log(
　　who varchar2(30),
　　when date);

　　3、 在employees_copy表上建立语句触发器，在触发器中填充employees_log表。　

   　Create or replace trigger biud_employee_copy
　　Before insert or update or delete
　　On employees_copy
　　Begin
　　Insert into employees_log(Who,when)
　　Values( user, sysdate);
　　End;
　　/

　　4、 测试

   　update employees_copy set salary= salary*1.1;
　　select *from employess_log;

　　5、 确定是哪个语句起作用?

　　即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?

　　可以在触发器中使用INSERTING / UPDATING / DELETING条件谓词，作判断：　　

      begin
　　if inserting then
　　-----
　　elsif updating then
　　-----
　　elsif deleting then
　　------
　　end if;
　　end;
　　if updating(‘COL1’) or updating(‘COL2’) then
　　------
　　end if;

　　2.8、[试验]

　　1、 修改日志表

   　alter table employees_log
　　add (action varchar2(20));

　　2、 修改触发器，以便记录语句类型。　　

      then
　　l_action:=’Delete’;
　　else
　　raise_application_error(-20001,’You should never ever get this error.’);
　　Insert into employees_log(Who,action,when)
　　Values( user, l_action,sysdate);
　　End;Create or replace trigger biud_employee_copy
　　Before insert or update or delete
　　On employees_copy
　　Declare
　　L_action employees_log.action%type;
　　Begin
　　if inserting then
　　l_action:=’Insert’;
　　elsif updating then
　　l_action:=’Update’;
　　elsif deleting 
　　/

　　　3、 测试
　　

      insert into employees_copy( employee_id, last_name, email, hire_date, job_id) 
　　values(12345,’Chen’,’Donny@hotmail’,sysdate,12); 
　　select *from employees_log