Oracle DML触发器和DDL触发器实例详解

一、概念

触发器是Oracle数据库的对象,类似存储过程和函数。存储过程和函数需要用户显示调用才执行,而触发器是由一个事件来触发运行,当某个事件发生时会自动地隐式运行,不能被显示的调用。

触发器的本质是存储过程,发生特定事件时Oracle会执行触发器中的代码,它的组成可以分为三个部分:

1、触发器执行的条件,即触发器被触发的事件

2、执行触发器的时间,发生事件之前(before)或发生事件之后(after)

3、触发器要做的事情,就是触发器被触发以后具体想执行的任务(PL/SQL语句块)

Oracle的触发器分为 DML触发器、DDL触发器、替代触发器 和 系统触发器。

二、DML触发器

基于DML操作的触发器,细分又可以分为行触发器和语句触发器。

2.1、语句触发器

DML操作(insert、delete、update),不管SQL语句影响的记录是多少行,触发器只触发一次。

2.2、行级触发器

DML操作(insert、delete、update),SQL语句影响了多少行记录,触发器就触发多少次。

行级触发器用for each row关键字。

2.3、DML触发器语法:

create [or replace] trigger 用户名.触发器名
       {before|after}      
       {delete|insert|update|[of列名]}
       on 表名
       [for each row [when 条件]]
declare
  定义变量。
begin
  PL/SQL语句块。                         
end;

参数说明:

{before|after}: 指定触发器是在对表的操作发生之前触发还是之后触发。

{delete|insert|update|[of列名]}: 触发在动作,可以指定多个动作,例如:insert or update。如果是update,update of 指定一个或多个字段,仅在这些字段被更新时才会触发。update of 的应用场景极少。

[for each row]: 表示是行级触发器。

[when 条件]: 只有满足when指定的条件,才会执行触发体中的代码,应用场景极少。

2.4、触发器谓词:

创建超女基本信息表T_GIRL,插入5条测试数据。

old谓词:执行前的字段的值的名称,比如update一个表时,使用:old.columnname是指执行update操作之前的列的值。

new谓词:执行后的字段的值的名称,比如update一个表时,使用:new.columnname是指执行 update操作之后的列的值。

可以在触发器体的语句块中使用 inserting、updating、deleting谓词,这些谓词会返回相应的DML操作的布尔值,如果为true,则表示执行了相应的insert、update、delete操作。

2.5、实例说明

1)准备测试数据,创建超女基本信息表T_GIRL,插入5条测试数据。

drop table T_GIRL;
create table T_GIRL
(
 id char(4) not null, -- 编号
 name varchar2(30) not null, -- 姓名
 primary key(id) -- 指定id为表的主键
);
insert into T_GIRL(id,name) values('0101','西施');
insert into T_GIRL(id,name) values('0102','貂禅');
insert into T_GIRL(id,name) values('0103','妲已');
insert into T_GIRL(id,name) values('0104','芙蓉姐姐');
insert into T_GIRL(id,name) values('0105','神密猫女');

2)创建SQL日志表。

drop table T_SQL_LOG;
create table T_SQL_LOG
(
 tname varchar2(10), -- 原表的表名。
 srcrowid rowid, -- 原表rowid。
 sqltype number(1), -- SQL语句的类型:1-insert、2-update、3-delete。
 trname varchar2(10) -- 触发器名。
);

3)创建语触发器TR_GIRL_1,如果对T_GIRL表做了insert、update和delete操作,把操作记录在T_SQL_LOG表中。

create or replace trigger TR_GIRL_1
 before update or delete or insert
 on T_GIRL
begin
 if inserting then
 insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',null,1,'TR_GIRL_1');
 end if;
 if updating then
 insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',null,2,'TR_GIRL_1');
 end if;
 if deleting then
 insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',null,3,'TR_GIRL_1');
 end if;
end;

4)创建行级语触发器TR_GIRL_2,如果对T_GIRL表做了insert、update和delete操作,把每一行的操作记录在T_SQL_LOG表中。

create or replace trigger TR_GIRL_2
 before update or delete or insert
 on T_GIRL for each row
begin
 if inserting then
 insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',:new.rowid,1,'TR_GIRL_2');
 end if;
 if updating then
 insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',:new.rowid,2,'TR_GIRL_2');
 end if;
 if deleting then
 insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',:old.rowid,3,'TR_GIRL_2');
 end if;
end;

5)执行一条delete语句,从T_GIRL表中删除两行记录。

delete from T_GIRL where id in ('0101','0102');

6)查看T_SQL_LOG表中的结果。

三、DDL 触发器

当执行DDL语句时会被触发。按照作用范围,分为schema trigger 和 database trigger。schema trigger 作用在一个用户上,database trigger 作用在整个数据库所有用户上。

常用的DDL操作有:grant(授权),revoke(撤销授权),create(创建),drop(删除),alter(修改),comment(注释),audit(审核),rename(重命名)等。

3.1、DDL触发器语法

create [or replace] trigger 用户名.触发器名
       {before|after} {DDL事件} on {database|schema}
       [when 条件]
declare
  定义变量。
begin
  PL/SQL语句块。
end;

参数说明:

{before|after}: 触发器是在DDL事件之前、之后触发。

{database|schema}: 作用在一个用户上,还是全部的用户。

[when 条件]: 只有满足when指定的条件,才会执行触发体中的代码,应用场景极少。

3.2、DDL 事件

3.3、可用属性

3.4、实例说明

限制scott用户的DLL操作,创建数据库对象时发出警告,删除数据库对象时阻止。

1)创建触发器

create or replace trigger scott.no_drop
 before ddl on schema
begin
 if ora_sysevent='CREATE' then
 dbms_output.put_line('Warning !!! You have created a '||
 ORA_DICT_OBJ_TYPE||' called '||
 ORA_DICT_OBJ_NAME|| '; UserName:'||
 ORA_DICT_OBJ_OWNER||'; IP:'||
 ORA_CLIENT_IP_ADDRESS||'; event:'||
 ORA_SYSEVENT);
 elsif ora_sysevent='DROP' then
 RAISE_APPLICATION_ERROR(-20000,'Cannot drop the '||
 ORA_DICT_OBJ_TYPE||' named '||
 ORA_DICT_OBJ_NAME ||' as requested by '||
 ORA_DICT_OBJ_OWNER);
 end if;
end;

2)测试触器(创建表),用scott用户登录。

在上面创建的触发器中用到了dbms_output,在sqlplus中要先执行set serveroutput on;才能输出内容。

3)测试删除表,用scott用户登录。

4)测试删除表,用DBA用户登录,no_drop触发器只限scott用户,不限制其它用户。

总结

作者:何以解忧,唯有..原文地址:https://blog.csdn.net/KevinChen2019/article/details/127504154

%s 个评论

要回复文章请先登录注册