欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle触发器的概念以及写法讲解

程序员文章站 2022-04-22 10:15:14
1. 触发器的概念: 触发器是存储在服务器中的程序单元,当数据库中某些事件发生时(比如insert\update\delete),数据库自动启动触发器,执行触发器中的相应操作。...

1. 触发器的概念:

触发器是存储在服务器中的程序单元,当数据库中某些事件发生时(比如insert\update\delete),数据库自动启动触发器,执行触发器中的相应操作。

(1) 触发器是一种特殊的存储过程,具备事务的功能;

(2) 触发器不能被直接调用,而是由事件来触发;--例如:增删改

(3) 触发器常用于加强数据的完整性约束和业务规则等。

简单点说:触发器就是个开关,灯就是开关触发后的操作,触动了开关灯就亮了。

2. 触发事件:

Insert,update,delete,create(创建对象时),alter,drop

logon/logoff(用户的登录或注销时执行触发器)

startup/shutdown(数据库打开或关闭时执行触发器)。

3. 触发时间

before 在指定的事件发生之前执行触发器

after 在指定的事件发生之后执行触发器

4. 触发级别

4.1行级触发

行触发:对触发事件影响的每一行执行触发器,即触发机制是基于行的。改一行数据,触发一次。

该类型触发器将在insert\update\delete操作完成以后触发,即after。(也就是增删改都用after)

关键语句:for each row

--案例2

create or replace tirgger t_mydel

after delete

on stuinfo

for each row

begin

dbms_output.put_line('删的好!');

end;

--执行(删除了一条记录,结果显示一条'删的好')

delete from stuinfo where stuname='李文才';

--执行(删除了整个表中的记录,显示N条'删的好')

delete from stuinfo

--说明:这就是讲解行级触发器。

--行级触发器:一条条的删,删一条就触发一个行级触发器。

--语句级触发器:执行一条触发一次。

4.2语句触发

语句触发:对触发事件只能触发一次,而且不能该问受触发器影响的每一行的值。既无论这条SQL语句影响多少条记录,触发器都只触发一次。

5. 创建触发器

create [or replace] trigger trigger_name

after|before|instead of --instead反向

[insert][[or] update [of 列列表]][[or] delete]

on table表或view

[for each row] --行级模式

Begin

--pl/sql语句(begin...end)

End;

案例1:

--创建一个触发器tig_1,当用户删除scott.emp表中的数据时提示。

SQL>create or replace trigger tig_1

after delete

on scott.emp //注意:这里没有用for each row,即不管删除多少条,只触发一次该触发器

begin

if deleting then

dbms_output.put_line('有用户删除了emp表中的数据!');

end if;

end;

案例2:

--创建一个触发器tig_2,当表scott.dept中的deptno列的值发生变化时,自动更新表scott.emp中的deptno列的值,从而保证数据的完整性。

SQL>create or replace trigger tig_2

after update

on scott.emp

for each row

begin

update scott.emp set deptno = :new.deptno

where deptno = :old.deptno;

end;

注意:

这段程序中有两个概念:new和:old, :new代表执行更新操作之后的新表,:old代表执行更新操作之前的旧表。

通过这两张表的使用,可以访问到触发器执行前后表数据的变化。

insert操作只有:new,delete操作只有:old,update操作二者皆有。

:new 和 :old只用于行级触发器。

--:new表,将插入的数据先放入到:new表中,确认后放到要更新的表。

--:old表,将不要的数据先放入到:old表中,确认不要了再清除:old表。

--注意::new表和:old表中至始至终就只有一条数据,那请问有多少个列?触发器的表有多少个列,:new表和:old表就有多少个列。

--案例:

SQL>insert into emp values(…,张三,…,0,……);

SQL>create or replace trigger trig_name

after insert --插入操作之后

on emp --在emp表中

for each row --行级模式

begin

if(:new.sal<=0) then --新表:new中的sal等于0

dbms_output.put_line('警告:干活不能不给薪水');

rollback;(可以用异常:抛异常后回滚数据)

else

dbms_output.put_line('已插入记录');

commit;

end if;

end;

6.触发器组成三部分:

a. 触发器语句(事件)--定义激活触发器的DML事件和DDL事件;

b. 触发器限制 --执行触发器的条件,该条件为真才能激活触发器;

c. 触发器操作(主体)--包含SQL语句和代码,它们在发出了触发语句且触发限制的值为真是才运行。

注示:序列通过前触发保存到数据库中。

案例:在级联表中创建触发器案例

--李斯文不能删

(什么时候触发?a.delete时; b.when 条件满足时; c.代码中写明)

SQL>create or replace trigger t_studel

after delete --删除操作之后

on stuinfo

for each row

begin

if :old.stuname='李斯文' then --如果:old表中存在李斯文,就提示不能删

--抛出异常

raise_application_error(-20010,'该学生不能删!!!');

end if;

end;

SQL>delete from emp;

--李斯文不能删也不能改

只需修改上例代码中的:

……

after delete or update

……

--怎么知道是(insert\delete\update)哪个操作?不知道没关系,用case when语句

……

begin

case

when deleting then

--删除时

if :old.stuname='李斯文' then

raise_application_error(-20010,'该学生不能删!!!');

end if;

when updating then

--修改时

if :old.stuname='李斯文' then

raise_application_error(-20011,'该学生不能修改!!!');

end if;

when inserting then

--插入时

if :new.stuname='张杨' then

raise_application_error(-20012,'该学生你也敢招!!!');

end if;

end case;

end;

7.多表连接触发器

在stuinfo表中写个delete触发器,指定删除的人名,

直接删除stumarks表中该人的成绩。

create or replace tirgger t_mydel

after delete

on stuinfo

for each row

declare

stu varchar2(22);

begin

stu:=:old.stuno; --将删除的学生的学号赋值给该变量

delete from stumarks where stuno=stu;

end;

--运行下

delete from stuinfo where stuname='李斯文';

8. 触发器的应用(标识列):

标识列:序列+触发器

--解决:

(1)建表

create table tb_715(

sid number,

sname varchar2(22)

)

(2)创建序列

create sequence seq715;

(3)创建个触发器,用前触发,用new表

create or replace trigger t_insert

before insert

on tb_715

for each row

begin

--新表中的id=序列的下一个值

:new.sid:=seq715.nextval;

end;

*****************************************

oracle 11g的语法:

:new.sid:=seq715.nextval;

oracle 10g的语法:

select seq715.nextval into :new.sid from dual;

别用变量,用变量的方法就是11g的写法。

Oracle触发器分:前触发、后触发