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

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触发器分:前触发、后触发

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐