玩技术,Geeker
一个原创技术文章分享网站

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语句每执行一次,语句级触发器就被触发一次。
  • BEFOREAFTER触发器
    BEFORE表示在触发语句运行前先运行“触发动作”。AFTER表示触发语句运行之后才运行“触发动作”。BEFOREAFTER适用于行级触发器和语句级触发器。
  • 复合触发器
    复合触发器是表上的触发器,它有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),用于判断触发触发器的是INSERTUPDATE还是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日 于呼和浩特。

打赏

未经允许不得转载:果冻想 » Oracle学习笔记——触发器

分享到:更多 ()

评论 1

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  1. #1

    Use trigger when necessary.

    Pan.8个月前 (11-16)回复

在这里玩技术,享受技术带来的疯狂

捐赠名单关于果冻