Oracle 触发器

Oracle触发器是使用者对Oracle数据库的对象做特定的操作时，触发的一段PL/SQL程序代码，叫做触发器。触发的事件包括对表的DML操作，用户的DDL操作以及数据库事件等。

一、触发器的作用

Oracle触发器可以根据不同的数据库事件进行特定的调用触发器程序块，因此，它可以帮助开发者完成一些PL/SQL存储过程完成不了的问题，比如操作日志的记录、防止一些无效的操作、校验数据的正确性、限制一些对数据库对象的操作、提供数据同步的可行性。但是不推荐在触发器当中写业务逻辑程序，因为这样对后期数据的维护将大大提高成本。

二、触发器的类型

触发器按照用户具体的操作事件的类型，可以分为5种触发器。大致如下：
1、数据操作（DML）触发器：此触发器是定义在Oracle表上的，当对表执行insert、update、delete操作时可以触发该触发器。如果按照对表中行级数据进行触发或语句级触发，又可以分为行级（row）触发器，语句级触发器，按照修改数据的前后触发触发器，又可以分为 after 触发器和before触发器之分。
2、数据定义操作（DDL）触发器：当对数据库对象进行create、alter、drop操作时，触发触发器进行一些操作记录保存、或者限定操作。
3、用户和系统事件触发器：该类型的触发器是作用在Oracle数据库系统上，当进行数据库事件时，触发触发器，一般用来记录登录的相关信息。
4、INSTEAD OF 触发器：此类型的触发器是作用在视图上，当用户对视图进行操作时，触发该触发器把相关的操作转换为对表进行操作。
5、复合触发器：指的是对数据操作(DML)触发器当中的多种类型触发器进行复合，比如；一个触发器当中包含着after（或before）的行级触发器和after（或before）的语句级触发器，来完成一些更为复杂的操作。

案例1、创建一个简单的触发器来校验学生基本信息的正确性，代码如下：

create or replace trigger tr_xsjbxx_insert
  before insert on stuinfo
  for each row
begin
  --对性别的数据进行校验
  if :new.SEX not in ('1', '2') then
    raise_application_error(-20001, '性别错误,请正确选择。');
  end if;
end;
 
insert into STUINFO
  (STUID,
   STUNAME,
   SEX,
   AGE,
   CLASSNO,
   STUADDRESS,
   GRADE,
   ENROLDATE,
   IDNUMBER)
values
  ('SC201801006',
   '张三丰',
   '3',
   26,
   'C201801',
   '福建省厦门市XXX号',
   '2018',
   to_date('01-09-2018', 'dd-mm-yyyy'),
   '3503021992XXXXXXXX');

结果如下：

代码解析：这是一个DML触发器，是对学生信息表（stuinfo）学生数据插入（insert）之前做的一个性别的校验，当性别的值不符合规范的时候报数据错误。

三、DML类型触发器

Oracle DML类型触发器是Oracle开发过程当中最经常用到，也是最常见的触发器，主要是对DML操作，如：insert、delete、update操作事件进行触发。

DML类型触发器安装触发的事件的前后和数据触发的类型可以分为四类：前置行级触发器、后置行级触发器、前置语句级触发器、后置语句级触发器。

创建DML类型触发器的语法结构如下：

create [ or replace] trigger tr_name(触发器名)
 
before|after
 
delete| insert | update [of column1,column2...]
 
[or delete |insert| update of colum1,colum2...]
 
on table_name(表名) 
 
[for each row]
 
[follows tr_name1(其它触发器名)]
 
[when 条件] 
 
declare
--声明部分
begin
--触发器内容部分
end;

语法解析：

1、or replace :存在同名的触发器就覆盖保存。

2、trigger：创建触发器的关键词。

3、before|after表示是选择的触发器是数据改变之前触发、数据改变之后触发。

4、delete| insert | update:表示触发器触发的事件类型是删除、插入或更新。

5、for each row: 表示行级触发器、不填就是语句级触发器

6、follows :表示触发器的顺序是跟在哪个之后。

7、when 表示触发器语句触发的条件

行级触发器

行级触发器一般用来做数据的校验或者记录数据的操作日志，下面是一个行级触发器的例子：

案例1、利用行级触发器记录更新学生信息表时的操作记录，代码如下：

create or replace trigger tr_stuinfo_update
  before update on stuinfo
  for each row
begin
  --当学生班号发生变化时,
  if :new.CLASSNO <> :old.CLASSNO then
    --插入操作日志表
    insert into oplog
      (LOGID, --日志ID
       TABLENAME, --表名
       COLNAME, --列名
       NEWDATA, --改变后数据
       OLDDATA, --改变前数据
       OPDATE, --操作时间
       OPERATOR) --操作人
    values
      (pk_oplog_id.nextval,
       'stuinfo',
       'classno',
       :new.classno,
       :old.classno,
       sysdate,
       'jsq');
  end if;
end;

代码解析：

1、这是一个学生信息表（stuinfo）update的前置行级触发器，当修改学生的班号时，会把修改的记录的操作信息记录在日志表（oplog）中。

2、行级触发器通过：new和：old来访问变化之后的数据和变化之前的数据，update类型触发器，新旧数据都可以访问，delete类型触发器，只能访问：old值，insert类型触发器只能访问：new值。

建立好触发器，我们更改一条数据看下效果，代码如下：

update stuinfo t set t.classno = 'C201802' where t.stuid = 'SC201801006';
select * from oplog;

结果如下：

语句级触发器

语句级触发器一般是用来做特定限制语句操作的作用，比如在某一段时间内禁止某一部分语句操作，下面是一个语句级触发器的案例：

案例2、比如今天是12月15号，我就禁止每月的15号禁止操作学生信息表（stuinfo）的插入和删除或修改操作。代码如下：

create or replace trigger tr_stuinfo_sql
  before update or insert or delete on stuinfo
begin
  --每月15号禁止操作学生信息表
  if to_char(sysdate,'dd')='15' then
    raise_application_error(-20001,'每月15号不能对学生信息表进行正删改操作！'); 
  end if;   
end;

代码解析：

1、DML语句触发器就是行级触发器省略掉for each row的写法。

2、raise_application_error是主动给客户端抛出-20001代码错误的信息。

建立好触发器，我们更新一条数据看下效果，结果如下：

总结：

同一个对象上可以有多个DML触发器，但是触发器触发的时候有先后顺序，比如before型触发器比after型触发器先触发，在此基础上行级触发器，比语句级触发器更早触发。同类型的触发器的先后顺序就按follows关键词+触发器名进行排序。

四、DDL类型触发器

Oracle DDL类型触发器主要是对于Oracle数据库的DDL操作触发的触发器，主要包括create、drop、alter等DDL事件，经常利用DDL类型触发器记录DDL操作记录或者限定对某个对象进行DDL操作。也可以根据对应DDL操作做对应的操作。

Oracle DDL类型触发器的语法结构

DDL类型触发器的编写语法如下：

create [ or replace] trigger tr_name(触发器名)
before|after
ddl_event|database_event
on SCHEMA(数据库对象)|DATABASE(数据库)
[follows tr_name1(其它触发器名)]
[when 条件] 
declare
--声明部分
begin
--触发器内容部分
end;

语法解析：

1、or replace :存在同名的触发器就覆盖保存。

2、trigger：创建触发器的关键词。

3、before|after表示是选择的触发器是在进行DDL操作之前触发还是之后触发。

4、ddl_event：表示的DDL事件，有create（创建）、alter(修改)、drop（删除）等常用DDL操作。

5、SCHEMA|DATABASE：表示触发器是作用在数据库对象上还是数据库上。

6、follows :表示触发器的顺序是跟在哪个之后。

7、when 表示触发器触发的附带条件，比如时间。

下面通过一个案例来解析Oracle DDL类型触发器的写法：

案例1、利用Oracle DDL类型触发器给学生信息表（stuinfo）做一个禁止删除、修改表结构的触发器，代码如下：

create or replace trigger tr_stuinfo_ddl
  before alter or drop  on schema
begin
  --禁止对学生信息表进行删除和修改操作
  if dictionary_obj_name = 'STUINFO' THEN
    --修改表结构
    if sysevent = 'ALTER' then
      --抛出错误
      raise_application_error(-20001,
                              '禁止学生信息表stuinfo进行alter操作！');
    end if;
   
    --删除表结构
    if sysevent = 'DROP' then
      --抛出错误
      raise_application_error(-20001,
                              '禁止学生信息表stuinfo进行drop操作！');
    end if;
  END IF;
end;

执行完案例1触发器，我们通过修改学生信息表（stuinfo）测试一下触发器的效果，代码如下：

--修改表结构
alter table STUINFO modify stuaddress VARCHAR2(200);
--删除表结构
drop table stuinfo;

结果如下：

案例2、利用DDL类型触发器的创建一个数据库级别的触发器，记录用户登录数据库的记录信息。再次我们需要设计一个登录记录表，来保存用户登录信息，代码如下：

-- Create table
create table LOGIN_LOG
(
  logid     VARCHAR2(20),
  loginuser VARCHAR2(100),
  logindate DATE
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table LOGIN_LOG
  is '登录日志表';
-- Add comments to the columns 
comment on column LOGIN_LOG.logid
  is '日志id';
comment on column LOGIN_LOG.loginuser
  is '登录用户名';
comment on column LOGIN_LOG.logindate
  is '登入时间';

建立DDL类型触发器（数据库级）：

create or replace trigger tr_stuinfo_ddl_login
  after  logon--数据库系统事件
  on database
begin
  --插入登录日志表
insert into login_log
  (LOGID, LOGINUSER, LOGINDATE)
values
  (pk_oplog_id.nextval,sys.login_user, sysdate);
end;

建立好案例2触发器，我们通过登录数据库，然后查看下登录日志表，查看一下效果，结果如下：

转载本站内容时，请务必注明来自W3xue，违者必究。