《【2017年整理】触发器语法详解》由会员分享，可在线阅读，更多相关《【2017年整理】触发器语法详解（11页珍藏版）》请在金锄头文库上搜索。
1、Oracle 触发器语法关键字: oracle 触发器语法 一 Oracle 触发器语法触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。功能：1、 允许/限制对表的修改2、 自动生成派生列，比如自增字段3、 强制数据一致性4、 提供审计和日志记录5、 防止无效的事务处理6、 启用复杂的业务逻辑触发器触发时间有两种：after 和 before。1、触发器的语法：CREATE OR REPLACE TIGGER 触发器名 触发时间 触发事件ON 表名FOR EACH ROWBEGINpl/
2、sql 语句END其中：触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。触发时间：指明触发器何时执行，该值可取：before-表示在数据库动作之前触发器执行;after-表示在数据库动作之后出发器执行。触发事件：指明哪些数据库动作会触发此触器：insert：数据库插入会触发此触发器; update：数据库修改会触发此触发器;delete：数据库删除会触发此触发器。表 名：数据库触发器所在的表。for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。2、举例：下面的触发器在更新表 auths 之前触发，目的是
3、不允许在周末修改表：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 OFSTAFF_NAME ON CRM.T_SUB_USERINFO REFERENCING OL
4、D 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_
5、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.depa
6、rtment_id80 )限制不是必须的。此例表示如果列 department_id 不等于 80 的时候，触发器就会执行。其中的 new_value 是代表更新之后的值。1.4、触发操作是触发器的主体begin :new_mission_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
7、,Chen,Donny, sysdate, 12, ,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 或者组合上进行关联。但是无论使用什
8、么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论 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.
9、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 d
10、ual; 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 o
11、r 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
12、 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
13、 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 dont have access to modify this table.); End if; End; /即使 SYS，SYSTEM
14、用户也不能修改 foo 表2.7、试验对修改表的时间、人物进行日志记录。1、 建立试验表create table employees_copy as select *from hr.employees2、 建立日志表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
15、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 e
