oracle触发器

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

功能：
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑

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

一、触发器的语法：

Create [or replace] trigger [模式.]触发器名
Before| after insert|delete|(update of 列名)
On 表名
[for each row]
When 条件
PL/SQL块
触发器名：触发器对象的名称。

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

触发时间：指明触发器何时执行，该值可取：

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

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

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

insert：数据库插入会触发此触发器；

update：数据库修改会触发此触发器；

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

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

For each row的意义是：在一次操作表的语句中，每操作成功一行就会触发一次；不写的话，表示是表级触发器，则无论操作多少行，都只触发一次；

When条件的出现说明了，在DML操作的时候也许一定会触发触发器，但是触发器不一定会做实际的工作，比如when 后的条件不为真的时候，触发器只是简单地跳过了PL/SQL块；

二.new表和old表
:new 与: old：必须是针对行级触发器的，也就是说要使用这两个变量的触发器一定有for each row
这两个变量是系统自动提供的数组变量，:new用来记录新插入的值，old用来记录被删除的值；

使用insert的时候只有:new里有值；
使用delete的时候只有:old里有值；
使用update的时候:new和:old里都有值；
可以这样使用： dbms_output.put_line('insert trigger is chufa
dbms_output.put_line('new id is : '||:new.stui
dbms_output.put_line('new name is : '||:new.st
dbms_output.put_line('new se is : '||:new.se);
可以这样从数据字典中查看一个表上有哪几个触发器：

带有:old变量的行级delete触发器：
create or replace trigger tg_deletestudent
before delete on student
for each row
begin
dbms_output.put_line('old is: '||:old.stuid);
dbms_output.put_line('old name: '||:old.stuname);
end;
/

三.触发器类型：（行级触发器对DML语句影响的每个行执行一次）
触发器类型：

1、 行触发器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;

2、 语句触发器：（语句触发器对每个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;

3、INSTEAD OF触发
创建在视图上的触发器，就是替代触发器，只能是行级触发器；
为什么要用替代触发器？
假如你有一个视图是基于多个表的字段连接查询得到的；现在如果你想直接对着这个视图insert;那你一定在想，我对视图的插入操作
怎么来反应到组成这个视图的各个表中呢？事实上，除了定义一个触发器来绑定在对视图上的插入动作上外，你没有别的办法通过系统的报错而直接向视图中插入数据；这就是我们用替代触发器的原因；替换的意思实际上是触发器的主体部分把对视图的插入操作转换成详细的对各个表的插入；
实现在一定条件下不向表中进行操作的触发器也可以用 AFTER | FOR 触发器，方法是对系统临时表 inserted 表或者 deleted 表进行检查，发现不能操作的，便用 ROLLBACK 对表进行回滚。但是这种操作是有点问题的，一是如果同时进行其他操作的话，可能会被一起回滚，二是有点脱什么放什么的嫌疑，先干了，发现不行，再回退，是不是很好笑！

其实 INSTEAD OF 触发器是解决这类问题的最好办法。在对表进行操作时，总会产生 INSERTED 和（或）DELETED表，不管这个操作是否已经进行。这里的和/或，要看进行的什么操作，插入，产生 INSERTED 表，删除，产生DELETED表，而update则两个都会产生。

如果不满足条件就不能插入，好，用 INSTEAD OF 触发器，利用 INSERTED 表先检查是否满足条件，如满足条件，那就只要： insert into 目标表 select * from inserted 就OK了，否则，可以什么都不做，由插入程序去根据 @@ROWCOUNT判断插入是否成功。当然，也可以用select '提示' 的方法给出提示。

如果不满足条件就不能删除，好，用 INSTEAD OF 触发器，利用 DELETED 表先检查是否满足条件，如满足条件，只要： delete from 目标表 where id in(select id from deleted) 就OK了，不满足条件也是什么都不做或给出提示，注意这里的 id 列必须是标识列，可不能删错了啊。

如果不满足条件就不能更新，同样用 INSTEAD OF 触发器（怎么有点儿像葛优在做广告？我最烦这人了！），这时两个系统临时表都可以派到用场，如果要检查更新前记录的某种条件，那可以用 DELETED 表，如果要检查更新后记录的某种条件，那可以用 INSERTED 表，想用什么就用什么，多方便！

好了，看个实际例子吧，也是坛子上有人提问的，实际上也是很经典的问题：

问1：有个借书系统，每人限借五本，超出的话不能借，如何通过触发器对借书进行限制？

问2：如果有人尚有借书，则此人不能被删除，如何用触发器对删除进行限制？

回复如下（表名，列名也是从问题帖子上得来的，记录是自己加的）：

create table usertb(userid int,借书证号 int)
--读者表,姓名等其他信息就免了
insert into usertb select 1,11 union all select 2,22 union all select 3,33
--插入有三条记录,三个读者

create table flow(bookid int ,借书证号 int)
--借书表,典型的纽带表,其另一主表book未写,但对本例不受影响

insert into flow
select 2483,22 union all
select 1524,11 union all
select 583,11 union all
select 374,11 union all
select 348,11 union all
select 347,11
--1,2两个用户都借着书,其中1号用户已借5本
go

--防删除触发器,尚有借书的读者不可被删除
create trigger tri_student
on usertb
INSTEAD OF delete
as
begin
--要到借书表中去找,假设你的 flow就是借书表:
if not exists(select 1 from flow a inner join deleted b on a.借书证号=b.借书证号)
delete from usertb where userid in(select userid from deleted)
--否则给出提示:(也可以不给出提示,在插入过程中可以用@@ROWCOUNT 判断是否有记录改变
else
select '尚有借书,不能删除!'
end
go

--防超借触发器:
create trigger js on flow
INSTEAD OF INSERT
as
begin
--在本表中查找,判断是否还能借
if not exists(select 1 from inserted a inner join flow b on a.借书证号=b.借书证号 group by a.借书证号 having count(*)>=5)
insert into flow select * from inserted
--提示
else
select '已满五本!'
end
go

--测试1:删除用户
delete from usertb where userid=2
select * from usertb
/*
--删除失败,用户还在:
userid 借书证号
----------- -----------
1 11
2 22
3 33

(3 行受影响)

*/
delete from usertb where userid=3
select * from usertb
/*
--删除成功
userid 借书证号
----------- -----------
1 11
2 22

(2 行受影响)

*/

--插入借书:
insert into flow select 2344,11
select * from flow where 借书证号=11
/*
--书号 2344 插入失败,已有五条记录
bookid 借书证号
----------- -----------
1524 11
583 11
374 11
348 11
347 11

(5 行受影响)
*/
insert into flow select 165,22
select * from flow where 借书证号=22
/*
--插入成功, 书号 165 被2号借去
bookid 借书证号
----------- -----------
2483 22
165 22

(2 行受影响)

*/
go
drop table usertb,flow

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/qianjin036a/archive/2011/01/13/6136588.aspx


4、 系统条件触发器

5、 用户事件触发器