前言
最近要做一个日志记录功能,记录一些表的操作记录,我的第一想法就是使用触发器来实现。虽然想到了触发器,但是自己还真没有办法立刻动手写出触发器,对于触发器的语法都有很多地方不熟悉,甚至不理解。借着这次机会,好好的把触发器又温习了一篇,所以总结成文,以便后期查阅。
触发器简介
触发器是存储在数据库服务器中的程序单元,当一个表或一个视图被改变,或者数据库发生某些事件时,Oracle会自动触发触发器,并执行触发器中的代码。只有在触发器中定义的事件发生时,触发器才被触发。触发器是自动执行的代码块,和存储过程的区别在于,用户可以直接调用存储过程,而不能直接调用触发器。
触发事件
能够触发触发器的事件有以下几种:
- DML操作(INSERT、UPDATE、DELETE)
- DDL操作(CREATE、ALTER、DROP)
- 系统事件(数据库的关闭与启动等)
- 用户事件(用户的登陆等)
上述的这些语句都可以触发触发器。如果你想在这些事件发生时干些别的事情,这个时候只需要定义对应的触发器即可,在触发器中完成你的工作。
触发器的组成
一个触发器由三部分组成:
- 触发事件或语句
触发事件或语句可以是SQL语句、数据库事件、用户事件。这些事件引起触发器被触发; -
触发限制
触发器限制是一个布尔表达式,当触发器触发时,会判断该布尔表达式;当布尔表达式的值为Unknown或者False,“触发动作”将不会被执行;当布尔表达式的值为True时,将会执行触发动作语句。触发限制是用WHEN子句来指定的。 -
触发动作
触发动作是一个PL/SQL过程块,由SQL语句和PL/SQL语句等组成。当触发限制为真时,它才被执行。
触发器的简单分类
触发器是基于表、视图、模式、数据库的,于此,我们可以把触发器分为下面的几类:
- 行级触发器和语句级触发器
行级触发器,即触发机制是基于行的,当表中数据改变时,将触发行级触发器,改变一行数据,触发一次;改变N行数据,就会触发N次;
语句级触发器是基于语句级的,当一条SQL语句改变数据时,无论这条SQL语句影响多少条记录,语句级触发器都只触发一次。SQL语句每执行一次,语句级触发器就被触发一次。 -
BEFORE
和AFTER
触发器
BEFORE
表示在触发语句运行前先运行“触发动作”。AFTER
表示触发语句运行之后才运行“触发动作”。BEFORE
和AFTER
适用于行级触发器和语句级触发器。 -
复合触发器
复合触发器是表上的触发器,它有4个时间点,可以让我们针对不同的时间点指定不同的处理动作。这四个时间点分别如下:- 在触发语句执行前(BEFORE STATEMENT)
- 在触发语句执行后(AFTER STATEMENT)
- 在每行记录被修改之前(BEFORE EACH ROW)
- 在每行记录被修改之后(AFTER EACH ROW)
触发语句必须是DML。如果触发语句没有影响任何一行数据,并且也没有指定BEFORE STATEMENT和AFTER STATEMENT两个时间点,则触发器也不会被触发。
INSTEAD OF
触发器
有的视图,我们不能直接对其进行更新操作,但是我们可以再这种视图上建立触发器,利用触发器对视图的基表进行更新操作,这种类型的触发器就叫做“INSTEAD OF触发器”。-
系统级触发器
系统事件触发器是基于数据库系统的触发器,系统事件触发器与表、视图没有关系。系统事件包括数据库启动、关闭、服务器错误、数据库角色改变等。当这些事件发生时,就会触发系统事件触发器。可以通过系统事件触发器实现对数据库的审计。 -
用户级触发器
用户事件包括用户登录数据库、用户退出数据库、用户执行DDL/DML语句等。当这些事件发生时,会触发用户事件触发器。
如何编写触发器
在触发器中,最重要的是触发动作部分,实际完成工作的部分也就是触发动作。触发动作是一个PL/SQL块或者一个对子程序(存储过程和函数)的调用。
create trigger tri_p
before insert or update of id on tb_student
for each row
when (new.id <> '00813027')
call check_id(:new.id)
上面的代码创建了名为tri_p
的触发器,它的主体是子程序调用,调用存储过程check_id
。上面的代码是调用的子程序,如果触发主体不是一个子程序调用,而是一个PL/SQL块,则需要对PL/SQL块进行编码。进行PL/SQL编码时,需要注意以下几个方面的东西。
- 在PL/SQL主体中访问列的值
在触发器中,列的值分为触发语句执行前的值和执行后的值。如果触发器涉及嵌套表,还可以使用PARENT对父表进行引用。PL/SQL新值和旧值的引用形式如下:- :new.列名
- :old.列名
同时,需要注意的是,INSERT没有旧值,只有新值。DELETE没有新值,只有旧值。:new和:old只用于行级触发器。比如以下的代码:
create or replace trigger tri_tb_student after update on jelly.tb_student for each row begin dbms_output.put_line('old value:' || :old.name); dbms_output.put_line('new value:' || :new.name); end;
我运行语句:
update jelly.tb_student set name='Jelly' where id='00813017';
就会输出:
old value:JellyThink new value:Jelly
- 判断触发的SQL语句类型
定义触发器时,在一个触发器中,可以指定多个触发语句。但是在实际工作中,我们需要知道到底是哪个语句触发了触发器。Oracle提供了三个判断条件(INSERTING
、DELETING
、UPDATING
),用于判断触发触发器的是INSERT
、UPDATE
还是DELETE
操作。create or replace trigger dml_trg after insert or update or delete on jelly.tb_student begin if updating then dbms_output.put_line('updating data from tb_student'); elsif deleting then dbms_output.put_line('deleting data from tb_student'); elsif inserting then dbms_output.put_line('inserting data into tb_student'); end if; end;
编写几个简单的触发器
上面对触发器进行了简单的分类,并总结了编写触发主体时需要注意的事项,下面就对不同类型的触发器进行实际的操作。
使用CREATE TRIGGER命令创建触发器。用户可以在自己的模式下创建触发器,但是必须具有CREATE TRIGGER权限。用户也可以在其他模式下创建触发器,但是必须具有系统权限CREATE ANY TRIGGER。用户也可以创建系统级的触发器,但是必须具有ADMINISTER DATABASE TRIGGER权限。创建触发器的常规语法如下:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;
下面就来进行具体的代码编写:
- 创建行级触发器
create or replace trigger row_trg before update of name on jelly.tb_student for each row begin dbms_output.put_line('ID:' || :old.id || '=>' || :new.id); dbms_output.put_line('Name:' || :old.name || '=>' || :new.name); dbms_output.put_line('Sex:' || :old.sex || '=>' || :new.sex); dbms_output.put_line('Age:' || :old.age || '=>' || :new.age); end;
重点是
for each row
。 -
创建语句级触发器
create or replace trigger sentence_trg before delete or insert on jelly.tb_student begin dbms_output.put_line('Called Once'); end;
没有使用
for each row
,表示创建的语句级触发。当我运行以下语句:insert into jelly.tb_student select * from jelly.tb_student;
此时,这个触发器将只输出一次
Called Once
。 -
创建BEFORE触发器
参见上面的行级触发器和语句级触发器,都是使用的
BEFORE
触发器。 -
创建AFTER触发器
create or replace trigger before_trg after delete on jelly.tb_student begin dbms_output.put_line('After Trigger'); end;
和
BEFORE
触发器基本一样,就是将BEFORE
换成AFTER
了。 -
创建复合触发器
create or replace trigger compound_trigger for update of name on jelly.tb_student compound trigger info1 constant varchar2(200) := 'Before Statement'; info2 constant varchar2(200) := 'Before Each Row'; info3 constant varchar2(200) := 'After Each Row'; info4 constant varchar2(200) := 'After Statement'; before statement is begin dbms_output.put_line(info1); end before statement; before each row is begin dbms_output.put_line(info2); end before each row; after each row is begin dbms_output.put_line(info3); end after each row; after statement is begin dbms_output.put_line(info4); end after statement; end
触发器的简单管理
我们可以对触发器进行禁用和启用管理;如果实在不想要这个触发器了,也可以把这个触发器直接干掉。
操作 | SQL语句 |
---|---|
禁用触发器 | alter trigger before_trg disable |
启用触发器 | alter trigger before_trg enable |
删除触发器 | drop trigger before_trg |
总结
触发器的知识点还是蛮多的,而我这里总结的只是一些基本知识(皮毛),如果你想更全面的去了解和学习触发器,推荐这篇文章。
很多人都说触发器有很多缺点,为什么呢?看这里的讨论。毕竟触发器是在背后偷偷执行的,很多时候,我们去维护别的系统时,很多时候会被偷偷触发的触发器而搞晕;所以,你以后在使用触发器的时候,请三思。还是那句话,存在即合理。
2015年7月29日 于呼和浩特。