• 触发器


    数据库触发器是存储于数据库的命名PL/SQL语句块,当触发事件发生时它们会隐含执行。

    触发事件可以是如下任何一种:

    1> 处理数据库表的DML语句(如INSERT,UPDATE或者DELETE)。在触发事件发生之前或者之后,触发器会执行。

    2> 特定用户在特定模式下,或者任何用户执行的DDL语句(如CREATE或者ALTER)。这种触发器经常被用于审计目的。它们可以记录各种模式修改,何时执行,以及哪个用户执行的。

    3> 系统事件,如数据库启动或者关闭

    4> 用户事件,如登陆和注销。即可以定义一个触发器,在用户登陆数据库时记录用户名和登陆事件。

    创建触发器的通用语法如下所示:

    复制代码
    CREATE [OR REPLACE] TRIGGER Trigger_name
    {BEFORE |AFTER} Triggering_event ON table_name
    [FOR EACH ROW]
    [FOLLOWS another_trigger]
    [ENABLE/DISABLE]
    [WHEN condition]
    DECLARE
         declaration statements
    BEGIN
         executable statements
    EXCEPTION 
         exception-handling statements
    END;
    复制代码

    其中,trigger_name是触发器的名称。BEFORE或者AFTER指明触发器何时执行,即在触发事件发生之前,还是之后,trigger_event是针对数据库表的DML语句。table_name是与该触发器相关的数据库表的名称。子句FOR EACH ROW指行触发器。FOLLOWS选项,指定触发器被触发的顺序。这个选项适用于在相同表上所定义的,并且会在相同时间点执行的触发器。ENABLE和DISABLE子句指定触发器是在启用,还是禁用状态下被创建的。默认情况下是启用的。

    禁用或启用触发器,可用以下命令:ALTER TRIGGER trigger_name DISABLE/ENABLE;

    禁用某个表上的所有触发器,可用如下命令:ALTER TABLE table_name DISABLE ALL TRIGGERS.

    触发器按照不同的分法,有不同的分类,主要有以下两种:

    按触发的时间可分为:BEFORE触发器和AFTER触发器

    按触发的类型可分为:行触发器,语句触发器和INSTEAD OF触发器

    下面,我们结合具体的实例来演示不同的触发器。

    一、 BEFORE触发器

    范例一:

    复制代码
    create or replace trigger student_i
    before insert on student
    for each row
    BEGIN
      :NEW.student.id := student_seq.nextval;
      :NEW.created_by := USER;
      :NEW.created_date := SYSDATE;
    END;
    复制代码

    注意:触发器包含伪记录:NEW,使得你可以访问正被插入student表的数据行。为访问伪记录:NEW的单独成员,需要使用点符号:

            通过PL/SQL表达式访问序列是Oracle 11g的新特性。在Oracle 11g之前,只能通过以下方式获取:

            select student_seq.nextval into v_student_id from dual;

            :NEW.student.id := v_student_id

    范例二:

    复制代码
    create or replace trigger emp_update
    before update or delete or insert on emp
    for each row
    begin
       if updating or deleting or inserting then
          raise_application_error(-20001,'The table emp can not be modified');
       end if;
    end;
    复制代码

    该触发器的代码部分有三个布尔函数-updating,deleting,inserting,如果对这个表执行update操作,则函数updating的值为TRUE;如果对这个表执行delete操作,则函数deleting的值为TRUE。insert操作亦然。

    范例三:

    复制代码
    create or replace trigger emp_update
    before update of sal on emp
    for each row
    begin
       if updating then
           raise_application_error(-20001,'Salary can not be modofied');
       end if;
    end;
    复制代码

    可见,对某列也可使用触发器

    二、 AFTER触发器

    范例四:

    复制代码
    create or replace trigger dept_delete
    after delete on dept
    for each row
    begin
       if deleting then
          delete from emp where deptno = :old.deptno;
       end if;
    end;
    复制代码

    dept和emp表存在外键约束,即dept表的主键deptno是emp表的外键,当我们删除dept表的行记录时,如果待删除的deptno在emp表中存在对应的记录,则会报ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found。构造触发器如上,当我们删除dept的行记录时,会自动删除deptno在emp表中对应的行记录。

    三、 自治事务

           trigger语句块中不能含有事务处理语句,如commit和rollback。倘若需要需要事务处理语句,则必须用到自治事务。

    四、 行触发器

           行触发器指的是触发器被触发的次数等同于触发语句所影响的数据行数量。当语句FOR EACH ROW出现在CREATE TRIGGER子句中,该触发器就是行触发器。

           上述范例均属于行触发器。

    五、 语句触发器

           对于语句触发器而言,每执行一次触发语句,该触发器就会执行一次,也就是说,不管触发语句影响多少数据行,该触发器只会执行一次。

           当该触发器所执行的操作不依赖于单独记录中的数据时,就应该使用语句触发器。例如,如果希望限制只能在上班时间访问emp表,就应该使用语句触发器。

    范例五:

    复制代码
    create or replace trigger emp_record
    before insert or update or delete on emp
    declare
       v_day varchar2(10);
    begin
       v_day := rtrim(to_char(sysdate,'DAY'));
       if v_day in ('SATURDAY','SUNDAY') then 
          raise_application_error(-20000,'The table can not be modified during off hours');
       end if;
    end;
    复制代码

    EMP表只能在工作日修改。

    六、 替代触发器

           到目前为止,我们所涉及的触发器都是基于表的,其实,Oracle还提供了另一种触发器,这种触发器是在数据库视图上创建的,即替代触发器。替代触发器会代替基于视图的DML操作(INSERT、UPDATE、DELETE),而直接作用于底层的数据库表。我们来看看下面的实验:

          1> 创建视图

           SQL> create or replace view v_test as select e.empno,e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

          2> 查询视图

           SQL> select * from v_test;

           EMPNO ENAME DEPTNO DNAME
           ---------- ---------- ---------- --------------
           7782 CLARK 10 ACCOUNTING
           7839 KING 10 ACCOUNTING
           ...

           3> 插入数据

            SQL> insert into v_test values(1234,'VICTOR',50,'IT');

            insert into v_test values(1234,'VICTOR',40,'IT')
            *
            ERROR at line 1:
            ORA-01776: cannot modify more than one base table through a join view

           4> 建立替代触发器

    复制代码
    CREATE OR REPLACE TRIGGER t_v_test
    INSTEAD OF INSERT ON v_test
    DECLARE
       duplicate_info EXCEPTION;
       PRAGMA EXCEPTION_INIT(duplicate_info,-00001);
    BEGIN
       INSERT INTO  dept(deptno,dname)
           VALUES(:new.deptno,:new.dname);
       INSERT INTO  emp(empno,ename,deptno)
           VALUES(:new.deptno,:new.ename,:new.deptno);
    EXCEPTION
       WHEN duplicate_info THEN 
           RAISE_APPLICATION_ERROR(-20001,'Duplicate empno or deptno');
    END;
    复制代码

           5> 重新插入数据 

            SQL> insert into v_test values(1234,'VICTOR',50,'IT');

            1 row created.

             测试OK!

    七、 系统触发器

           下面试举几例

           1> 限制用户从指定IP登陆

    复制代码
    CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
    AFTER LOGON ON DATABASE
    DECLARE
       RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
       ALLOWED_IP      VARCHAR2(16) := '192.168.1.1';
       LOGON_USER      VARCHAR2(32);
       CLIENT_IP       VARCHAR2(16);
    BEGIN
       LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');         -->> SYS_CONTEXT是一个蛮有用的函数
       CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
       IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
            RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
       END IF;
    END;
    复制代码

           2> 通过用户级别触发器修改日期格式

    复制代码
    CREATE OR REPLACE TRIGGER emp_format
    AFTER LOGON ON SCHEMA
    DECLARE
       sqlstr VARCHAR2(60) :='alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';   注意:连续两个单引号表示转义
    BEGIN
       execute immediate sqlstr;
    END;
    复制代码

            3> 记录用户的登陆信息

    复制代码
    SQL> create table log_table(sid number,serial# number,username varchar2(20),action varchar2(8),log_time varchar2(19));   -->> 首先创建表用于记录用户的登陆信息
    
    CREATE OR REPLACE TRIGGER logon_db
    AFTER LOGON ON DATABASE
    DECLARE
      v_sid v$mystat.sid%type;
      v_serial# v$session.serial#%type;
      v_username v$session.username%type;
    BEGIN
      SELECT sid INTO v_sid FROM v$mystat WHERE rownum=1;
      SELECT serial#,username
      INTO   v_serial#,v_username
      FROM v$session WHERE sid=v_sid;
      INSERT INTO log_table
      VALUES (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
    END logon_db;
    复制代码

     AFTER LOGON ON DATABASE 也可替换为 BEFORE SHUTDOWN ON DATABASE,BEFORE LOGON ON SCHEMA,ALTER LOGON ON SCHEMA等等。

    八、 复合触发器

    更多相关内容,可参考官方文档:http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS020

             

           

          

          

          

  • 相关阅读:
    springcloud 微服务 分布式 Activiti6 工作流 vue.js html 跨域 前后分离
    java 整合redis缓存 SSM 后台框架 rest接口 shiro druid maven bootstrap html5
    继承
    封装
    对象的生命周期
    类与对象
    如何理解类?
    面向过程
    jdk1.8新特性
    git使用指南
  • 原文地址:https://www.cnblogs.com/xieweikai/p/6837985.html
一二三 - 开发者的网上家园