DML触发器学习
来了解一下触发器的建立方法，以及各类触发器的简单介绍。首先看DML触发器的语法：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column ...]]}
ON {[schema.] table_name | [schema.] view_name}
[PRFERENCING {OLD [AS] old | NEW [AS] new | PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
Trigger body;
说明：
1、BEFORE/AFTER：指触发时间在DML操作之前还是之后(对表配置约束时特别有用)
2、DML包括INSERT、DELETE、UPDATE(可以只针对某几列)
3、FOR EACH ROW：表明是行级触发器
DML触发器示例
1、创建信息表和最终表
create table t11(a int,b varchar2(100));
insert into t11 values(1,'aaa');
insert into t11 values(1,'bbb');
insert into t11 values(1,'ccc');
insert into t11 values(2,'ddd');
insert into t11 values(2,'eee');
insert into t11 values(3,'fff');
insert into t11 values(4,'ggg');
insert into t11 values(5,'hhh');
commit;
create table t1_log(
who varchar2(30),
poer_date date
);
2、创建触发器
create or replace trigger delete_trigger
after delete
on t11
for each row --行级触发器
begin
insert into t1_log values(user,sysdate);
end;
3、测试
delete from t11 where a=1;
SQL> select * from t1_log;
WHO POER_DATE
------- -------------------
WXQ 2008-11-2 14:04:38
WXQ 2008-11-2 14:04:38
WXQ 2008-11-2 14:04:38
注：若没有for each row语句，则只插入一行记录。
触发器的级联
create table t1_a(id int);
create table t1_b(id int);
create table t1_c(id int);
create trigger tr_a
after insert
on t1_a
begin
insert into t1_b values(1);
end; --插入t1_a时，插入t1_b
create trigger tr_b
after insert
on t1_b
begin
insert into t1_c values(1);
end; --插入t1_b时，插入t1_c
create trigger tr_c
after insert
on t1_c
begin
update tr_a set tr_a.id=tr_a.id+10;
end; --插入t1_c时，更新t1_a
测试：
SQL> insert into t1_a values(5);
1 row inserted
SQL> select * from t1_a;
ID
-----
15
多条件触发器
CREATE OR REPLACE TRIGGER
BEFORE INSERT OR UPDATE OR DELETE
BEGIN
IF inserting THEN
--insert语句触发
ELSIF updating THEN
--update语句触发
ELSIF deleting THEN
--delete语句触发
END IF;
END;
注：若要针对update某一列，则用 IF UPDATING('xx') THEN...
具体举例：
create or replace trigger oper_trigger
before insert or update or delete
on t1
declare
str_action varchar2(100);
begin
if inserting then
str_action:='Insert';
end if;
if updating then
str_action:='Update';
end if;
if deleting then
str_action:='Delete';
end if;
insert into t1_log values(str_action,sysdate);
end;
行级触发器特性
1、简单举例
create or replace trigger reco_trigger
after delete
on t11
for each row
begin
insert into t22 values(:old.a,:old.b);
end;
2、说明：
* 可以通过:new和:old来获得操作前后的不同数据映像
* update可同时使用:new、:old，insert只能用:new，delete只能用:old
3、通过REFERENCING修改映像标识符
create or replace trigger reco_trigger
before update
on t11
referencing new as my_value --注意格式
for each row
when (my_value.a > 10) --新值大于10时才触发
begin
insert into t22 values(:my_value.a,:my_value.b);
end;
注意：即使重命名了，但是还是要在前面加“:”，但是在referencing和when字句中不用加