一 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之前触发,目的是不允许在周末修改表:
on begin if(to_char(sysdate,'DY')='SUN' RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths'); end end |
例子:
ON REFERENCING FOR declare begin if begin |
- 客户投诉
|
- 客户关怀
where |
- 客户服务
where end; end end / |
二 Oracle触发器详解
开始:
beforeinsertorupdateofde referencingoldasold_value for when begin :new_value.commission_pct end; / |
1、触发器的组成部分:
1、 触发器名称
2、 触发语句
3、 触发器限制
4、 触发操作
1.1、触发器名称
|
命名习惯:
|
employees表名
department_id列名
1.2、触发语句
比如:
表或视图上的DML语句
DDL语句
数据库关闭或启动,startup shutdown等等
of on referencing new for |
说明:
1、无论是否规定了department_id,对employees表进行insert的时候
2、对employees表的department_id列进行update的时候
1.3、触发器限制
|
限制不是必须的。此例表示如果列department_id不等于80的时候,触发器就会执行。
其中的new_value是代表更新之后的值。
1.4、触发操作
是触发器的主体
:new_value.commission_pct end; |
主体很简单,就是将更新后的commission_pct列置为0
触发:
department_id,salary,commission_pct values( select |
触发器不会通知用户,便改变了用户的输入值。
触发器类型:
1、 语句触发器
2、 行触发器
3、INSTEAD OF触发
4、 系统条件触发器
5、 用户事件触发器
2.1、语句级触发器.(语句级触发器对每个DML语句执行一次)
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次。比如,无论update多少行,也只会调用一次update语句触发器。
实例:
after begin if dbms_output.put_line('修改'); elsif dbms_output.put_line('删除'); elsif dbms_output.put_line('插入'); end end; |
2.2、行级触发器.(行级触发器对DML语句影响的每个行执行一次)
实例一:
- 触发器
-
行级触发器
create create before for begin if select else raise_application_error(-20020,'不允许更新ID值!');--中断程序 end end; |
触发器是特定事件出现的时候,自动执行的代码块。类似于存储过程。本文继续介绍Oracle 触发器语法及实例。
- 测试,插入几条记录
insert insert |
- 创建一个触发器,无论用户插入新记录,还是修改emp表的job列,都将用户指定的job列的值转换成大写.
before on for begin if :new.job:=upper(:new.job); else :new.job:=upper(:new.job); end end; |
2.3、instead of触发器.
(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.)
语法如下:
instead referencing for declare .......... begin ........ end; |
2.4、模式触发器.
可以在模式级的操作上建立触发器.
实例如下:
after begin insert end; |
2.5、数据库级触发器.
可以创建在数据库事件上的触发器,包括关闭,启动,服务器错误,登录等.这些事件都是实例范围的,不与特定的表或视图关联.
实例:
after begin ........... end; |
2.6、例子:
需要对在表上进行DML操作的用户进行安全检查,看是否具有合适的特权。
Create Before On Begin If Raise_application_error(-20001, End End; / |
即使SYS,SYSTEM用户也不能修改foo表
2.7、[试验]
对修改表的时间、人物进行日志记录。
1、 建立试验表
|
2、 建立日志表
who when |
3、在employees_copy表上建立语句触发器,在触发器中填充employees_log表。
Before On Begin Insert Values( End; / |
4、 测试
select |
5、 确定是哪个语句起作用?
即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?
可以在触发器中使用INSERTING / UPDATING / DELETING条件谓词,作判断:
if ----- elsif ----- elsif ------ end end; if ------ end |
2.8、[试验]
1、 修改日志表
add |
2、 修改触发器,以便记录语句类型。
l_act else raise_application_error(-20001,’You Insert Values( End;Create Before On Declare L_act Begin if l_act elsif l_act elsif / |
3、
values(12345,’Chen’,’Donny@hotmail’,sysdate,12); select |