触发器学习笔记(:new，:old用法)

151 阅读 0 评论 100 点赞

我是靠谱客的博主孤独小猫咪，这篇文章主要介绍触发器学习笔记(:new，:old用法)，现在分享给大家，希望可以做个参考。

####触发器是数据库发生某个操作时自动运行的一类的程序

用于保持数据的完整性或记录数据库操作信息方面
触发器不能够被直接调用，只能够在某些事件发生时被触发，也就是系统自动进行调用

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE [ OR REPLACE] TRIGGER trigger_name

BEFORE | AFTER

event1 [OR event2 OR event3 …]

ON TABLE_NAME [FOR EACH ROW]

BEGIN

STATEMENT;

…

END;

event通常是insert、delete或update等DML操作

#####说明：

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

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

####触发器分为语句级触发器和行级触发器

语句级触发器是指每执行一条DML语句，该触发器就执行一次
行级触发器是指每个DML操作影响几条记录，就会触发几次(for each row)
行级触发器中由于涉及到了每条记录的数据变动，所以对于每条记录的数据来说就有新值和旧值之分

####关键字：

:NEW 和:OLD使用方法和意义，
new 只出现在insert和update时，old只出现在update和delete时。

insert时new表示新插入的行数据，
update时new表示要替换的新数据、old表示要被更改的原来的数据行，
delete时old表示要被删除的数据。

###示例(1)

记录操作数据库信息方面
创建一个t_emp2_log表用于存储用户对emp2表所做的操作信息

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
 create table t_emp2_log( 

          t_id  number(10) primary key,  --编号 

          t_date  date,     --修改日期 

          t_user  varchar2(20),  --操作用户 

          action varchar(20)   --该用户所做操作 

   );

创建触发器t_emp2，当有用户对表emp2操作的时候，就会触发该触发器，记录改用户对表emp2所做的操作

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 create or replace trigger t_emp2 

          after update or delete or insert on emp2 

   begin 

          if updating then --当执行更新操作时执行 

             insert into  t_emp2_log values(seq_t_emp2_log.nextval,sysdate, user,    'update'); 

          elsif deleting then --当执行删除操作时执行 

             insert into t_emp2_log values(seq_t_emp2_log.nextval,sysdate, user,    'delete'); 

          elsif inserting then -- 当执行插入操作时 

             insert into t_emp2_log values(seq_t_emp2_log.nextval, sysdate, user,           'inserting'); 

          end if;  

   end;

###示例(2)

保持数据的完整性…

复制代码

1
2
create table dept2 as select * from dept;

当存在依赖表时，当主表的信息发生改变时，依赖表的相应信息也会发生改变

复制代码

1
2
3
4
5
6
7
8
create or replace trigger tr_emp2 
--当主表dept2发生改变时, 
after update on dept2 for each row 
begin   
--依赖表也会发生相应的变化，以保持数据的完整性 
update emp2 e set e.deptno = :NEW.deptno where  e.deptno = :OLD.deptno; 
end;

###示例 (3)

####两个表字段完全一样

例如：emp 表 emp_ copy 表

####要求：写一个触发器

提示： :NEW :OLD

####功能实现

当往emp表内添加字段且deptno=10的时候往emp_copy表copy此数据

在emp表更新数据时且修改deptno=10 的时候往emp_copy表copy此数据

复制代码

--向emp_copy表copy新数据

--emp_copy可以存在相同的记录

CREATE OR REPLACE TRIGGER tr_emp_copy

AFTER INSERT OR UPDATE ON emp2 FOR EACH ROW

BEGIN

IF inserting THEN

IF :NEW.deptno = 10 OR :OLD.deptno = 10 THEN

INSERT INTO emp_copy VALUES (:NEW.empno, :NEW.ename, :NEW.job,

:NEW.mgr, :NEW.hiredate, :NEW.sal, :NEW.comm, :NEW.deptno);

END IF;

ELSIF updating THEN

IF :OLD.deptno = 10 THEN

INSERT INTO emp_copy VALUES (:NEW.empno, :NEW.ename, :NEW.job,

:NEW.mgr, :NEW.hiredate, :NEW.sal, :NEW.comm, :NEW.deptno);

END IF;

END;

--注意事项

不管是after ,还是 before  在update , insert , delete 时，一定是在事务提交之后才会触发触发器

before 和after的区别:before：insert update 可以对new进行修改。

after :不能对new 进行修改

二者都不能对old 进行修改

create or replace trigger tri_emp2

before update on dept2 for each row

begin

:NEW.deptno := 80;

update emp2 e set e.deptno = :NEW.deptno where e.deptno = :OLD.deptno;

end;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
--向emp_copy表copy新数据 

--emp_copy可以存在相同的记录 

CREATE OR REPLACE TRIGGER tr_emp_copy

AFTER INSERT OR UPDATE ON emp2 FOR EACH ROW


BEGIN


IF inserting THEN

IF :NEW.deptno = 10 OR :OLD.deptno = 10 THEN

INSERT INTO emp_copy VALUES (:NEW.empno, :NEW.ename, :NEW.job,


                             :NEW.mgr, :NEW.hiredate, :NEW.sal, :NEW.comm, :NEW.deptno);

END IF;

ELSIF updating THEN

IF :OLD.deptno = 10 THEN

INSERT INTO emp_copy VALUES (:NEW.empno, :NEW.ename, :NEW.job,


                             :NEW.mgr, :NEW.hiredate, :NEW.sal, :NEW.comm, :NEW.deptno);

END IF;

END IF;

END; 


   
 

--注意事项 

   /* 

     不管是after ,还是 before  在update , insert , delete 时，一定是在事务提交之后才会触发触发器 

     before 和after的区别:before：insert update 可以对new进行修改。 

                       after :不能对new 进行修改 

                      二者都不能对old 进行修改 

 

   */ 

 

   create or replace trigger tri_emp2 

          before update on dept2 for each row 

   begin 

          :NEW.deptno := 80; 

          update emp2 e set e.deptno = :NEW.deptno where e.deptno = :OLD.deptno; 

end;