触发器oracle 简易,Oracle学习笔记——触发器

最近要做一个日志记录功能，记录一些表的操作记录，我的第一想法就是使用触发器来实现。虽然想到了触发器，但是自己还真没有办法立刻动手写出触发器，对于触发器的语法都有很多地方不熟悉，甚至不理解。借着这次机会，好好的把触发器又温习了一篇，所以总结成文，以便后期查阅。

触发器简介

触发器是存储在数据库服务器中的程序单元，当一个表或一个视图被改变，或者数据库发生某些事件时，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新值和旧值的引用形式如下：

同时，需要注意的是，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:new.列名

:old.列名

判断触发的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

总结

触发器的知识点还是蛮多的，而我这里总结的只是一些基本知识(皮毛)，如果你想更全面的去了解和学习触发器，推荐这篇文章。

很多人都说触发器有很多缺点，为什么呢？看这里的讨论。毕竟触发器是在背后偷偷执行的，很多时候，我们去维护别的系统时，很多时候会被偷偷触发的触发器而搞晕；所以，你以后在使用触发器的时候，请三思。还是那句话，存在即合理。

本文来自投稿，不代表访得立场，如若转载，请注明出处：http://www.found5.com//view/545.html