Oracle：触发器
基本介绍
触发器不允许用户显式传递参数，不能够返回参数值，也不允许用户调用触发器。触发器只能由Oracle在合适的时机自动调用。
根据使用对象的分类：
-
一般是数据表：
语句触发器、 行触发器、 instead of 触发器 -
侧重于针对数据库和用户操作触发：
系统事件触发器、 用户事件触发器。
语法：
create trigger trigger_name on [dbo][table]
for insert,update,delete
as
sql_statement
案例：利用触发器强制设置新进员工的服务年限为0
create or replace trigger tr_before_insert_books
before insert
on books
for each row
begin
:new.publish_date:=to_date('2002-1-1','yyyy-mm-dd');
:new.press:='某某出版社';
end;
/
-- for each row表明触发器的作用细化为表的每一行；
-- begin end之间的部分为触发器执行时的代码块
-- :new.work_years:=0将插入行的work_yeas字段更新为0
SQL> select * from books;
BOOK_ID BOOK_NAME AUTHOR PRESS PUBLISH_DATE
---------- -------------------- ---------- -------------------- --------------
1 围城 钱钟书 人民文学出版社 09-4月 -06
2 千年一叹 余秋雨 作家出版社 01-5月 -05
3 平凡的世界 路遥 北京十月文艺出版社 01-3月 -09
4 老人与海 海明威 人民文学出版社 01-8月 -52
5 柳青传 柳青 中华书局 03-3月 -02
SQL> create or replace trigger tr_before_insert_books
2 before insert
3 on books
4 for each row
5 begin
6 :new.publish_date:=to_date('2002-1-1','yyyy-mm-dd');
7 :new.press:='某某出版社';
8 end;
9 /
触发器已创建
SQL> insert into books(book_id,book_name,author) values(6,'测试','测试1');
已创建 1 行。
SQL> select * from books;
BOOK_ID BOOK_NAME AUTHOR PRESS PUBLISH_DATE
---------- -------------------- ---------- -------------------- --------------
1 围城 钱钟书 人民文学出版社 09-4月 -06
2 千年一叹 余秋雨 作家出版社 01-5月 -05
3 平凡的世界 路遥 北京十月文艺出版社 01-3月 -09
4 老人与海 海明威 人民文学出版社 01-8月 -52
5 柳青传 柳青 中华书局 03-3月 -02
6 测试 测试1 01-1月 -02
6 测试 测试1 某某出版社 01-1月 -02
已选择7行。
注：创建语法：描述触发器、定义触发器的代码块；触发器描述和代码块中间不能出现分号。
触发器的作用级别：使用了for each row，将触发器的作用级别定义为行级。也只有此时的行级触发器才能使用:new变量
- 如何利用表级触发器限制插入操作的用户
create or replace trigger tr_before_insert_books
before insert on books
begin
if user!='ADMIN' then
raise_application_error(-20001,'权限不足，不能向数据表中插入数据');
end if;
end;
/
SQL> create or replace trigger tr_before_insert_books
2 before insert on books
3 begin
4 if user!='ADMIN' then
5 raise_application_error(-20001,'权限不足，不能向数据表中插入数据');
6 end if;
7 end;
8
9 /
触发器已创建
SQL> insert into books(book_id) values(7);
insert into books(book_id) values(7)
*
第 1 行出现错误:
ORA-20001: 权限不足，不能向数据表中插入数据
ORA-06512: 在 "SCOTT.TR_BEFORE_INSERT_BOOKS", line 3
ORA-04088: 触发器 'SCOTT.TR_BEFORE_INSERT_BOOKS' 执行过程中出错
如何在UPDATE和INSERT事件上同时定义触发器
create or replace trigger tr_insert_update_books
before insert or update on books
for each row
begin
-- 用于将新行的status列值修改为大写形式
:new.author:=UPPer(:new.author);
end;
/
增加触发器限制
create or replace trigger tr_before_update_books
before update on books
for each row
when (old.book_id='6')
begin
:new.book_id:=6;
end;
/
语句触发器
语句触发器是指建立在表上的，由表的特定操作触发的触发器。语句触发器是表级触发器，即无论操作影响了多少行数据，语句触发器只被调用一次。
create or replace trigger tr_emp
before insert or update on emp
begin
insert into emp(empno,ename) values(10001,user);
end;
/
insert into emp(empno,ename) values(10001,'SCOTT');
对于激活触发器的动作，Oracle提供了谓词，来判断触发动作的类型。常用的谓词包括：inserting、updating和deleting.某个触发器被激活时，Oracle总是自动更新这三种谓词的值，需要注意的是，三种谓词只能有一个为真，因为每次触发器被激活总是由于某个特定动作。
create or replace trigger tr_books2
before insert or update or delete on books
begin
if inserting then
dbms_output.put_line('插入操作');
end if;
if updating then
dbms_output.put_line('更新操作');
end if;
if deleting then
dbms_output.put_line('删除操作');
end if;
end;
/
对于验证用户权限的触发器应该使用before关键字，因为用户操作执行完毕再进行权限校验是没有任何意义的；对于记录log的触发器，则应该使用after关键字，这是因为update、insert、delete等操作有可能返回错误，事务需要回滚，那么用户没有进行实际操作，不必记录log，所以触发时机应该选择after。
触发器级联
触发器级联是指当某个事件激活了触发器时，该触发器的操作可能涉及另外一个表，而针对涉及表的操作可能激活建立在该表之上的触发器。如果该触发器的操作再次激活最初的触发器，将会造成死循环。因此，触发器的级联往往会造成不可预知的问题
行触发器
行触发器可以建立在insert操作之上，但此时触发器只能使用:new引用，而不能使用:old，因为insert操作并不存在旧的数据；但对于update操作，:new引用和:old引用都可以使用；delete操作则只能使用:old引用。
如何利用行触发器与:new引用保证插入数据的合法性。
create or replace trigger tr_before_insert_employee
before insert on t_employees
for each row
begin
select (max(employee_id)+1) into :new.employee_id from t_employees;
:new.status:=upper(:new.status);
:new.work_years:=0;
end;
/
对于:new和:old引用，可以使用referencing子句来指定别名，以便在使用时可以直接使用别名。
create or replace trigger tr_before_update_employee
before update on t_employees
referencing new as new_value
old as old_value
for each row
begin
declare num number;
begin
select count(1) into num from t_salary where employee_id:=old_value.employee_id;
if num>0 then
:new_value.status:='ACT';
else
:new_value.status='CXL';
end if;
end;
end;
/
