PL/SQL之--触发器

一、简介
　　触发器在数据库里以独立的对象进行存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来触发运行。oracle事件指的是对数据库的表或视图进行的insert、update及delete操作或对视图进行类似的操作。oracle将触发器的功能扩展到了触发ORACLE，如用户的登录、数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计相关的功能。

触发器的组成如下：

触发事件：在任何情况下触发trigger；如insert，update，delete

触发时间：该trigger是在触发事件发生之前（before）还是之后（after）触发，也就是触发事件和该trigger的操作顺序。

触发器本身：该trigger被触发之后的执行体。例如PL/SQL块。

触发频率：说明触发器内定义的动作被执行的次数。即语句级（statement）触发器和行级（row）触发器

　　语句级（statement）触发器：指当触发事件发生时，该触发器只执行一次；

　　行级（row）触发器：指当某触发事件发生时，对收到该操作一下的每一行数据，触发器都单独执行。
oracle触发器分为如下三种：
1、DML触发器 DML 数据操纵语言(Data Manipulation Language)
　　在DML语句(insert、update及delete)进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。
2、替代触发器
　　由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE 8专门为进行视图操作的一种处理方法。
3、系统触发器
　　ORACLE 8i 提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

二、DML触发器

　　oracle可以在dml语句进行触发，可以在dml操作前或操作后进行触发，并且可以对每个行（行级）或语句（级）操作上进行触发。DML触发器分为如下两种触发器：

行触发器：是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：

　　　　1、定义语句中包含FOR EACH ROW子句。
　　　　2、在BEFORE……FOR EACH ROW触发器中，用户可以引用受到影响的行值。

语句触发器：是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。无论update多少行，也只会调用一次update语句触发器。

　　创建触发器的一般语法如下：

　　create or replace trigger trigger_name
　　{before|after}
　　{insert|delete|update[of column]}
　　on [schema] table_name
　　　　[referencing {old[as] old|new[as]new|parent as parent}]
　　[for each row]
　　　
　　　　[when condition]
　　trigger body;

　　以下person表结构如下：

DROP TABLE person ;

CREATE TABLE person (

id NUMBER(11) NOT NULL ,

username VARCHAR2(255 ) NULL ,

age NUMBER(11) NULL ,

password VARCHAR2(255) NULL ,

PRIMARY KEY (id)

)

　　1、insert行级触发器

create or replace trigger trigger_insert

  before insert on person

　referencing new as new old as old

  for each row

declare

  v_username varchar2(50);

begin

  v_username := :new.username;

  :new.age := :new.age + 2; --插入的年龄加2

  dbms_output.put_line(v_username); -- 插入插入记录的username字段

end trigger_insert;

--添加数据

insert into person values (1, '张三', 20, 'zhangsan');

　　2、update行级触发器

create or replace trigger trigger_update_before

  before update on person

  referencing new as new old as old

  for each row

declare

  v_username varchar2(50);

begin

  v_username := :new.username; -- 记录新名字

  :new.age := :new.age + 3; -- 修改年龄

  dbms_output.put_line(v_username);

end trigger_update_before;

-- after update 触发器

create or replace trigger trigger_update_after

  before update on person

  referencing new as new old as old

  for each row

declare

  v_old_name varchar2(50);

begin

  v_old_name :=  :old.username;  -- 记录修改前的名字

  dbms_output.put_line(v_old_name);

end trigger_update_after;

-- 更新数据

update person set username='张三1', age = 19 where id = 1;

　　3、delete行级触发器

create or replace trigger trigger_delete

  before delete on person

  referencing new as new old as old

  for each row

declare

  v_old_name varchar2(50);

begin

  v_old_name :=  :old.username;  -- 获取被删除的用户名称

  dbms_output.put_line(v_old_name);

end trigger_delete;

--删除数据

delete from person where id = 1;

　　4、语句触发器

-- 记录操作表的信息

create table person_log(

  who varchar2(30),

  when date,

  action varchar2(50)

)

--触发器

create or replace trigger trigger_person_statment

  before insert or update or delete on person

declare

  -- 保存操作信息

  v_action person_log.action%type;

begin

  if inserting then

    v_action := 'Insert';

  elsif updating then

    v_action := 'Update';

  elsif deleting then

    v_action := 'Delete';

  else

   raise_application_error(-20001,'You should never ever get this error.');

  end if;

  insert into person_log(who,action,when)

  values(user, v_action,sysdate);

end trigger_person_statment;

-- 操作shuj

declare

begin

   insert into person values(1003, '', 20, '');

   insert into person values(1004, '', 20, '');

   update person set age = 19 where id >1000 and id <1003;-- 此时触发器之后执行一次

   delete from person where id >1000 and id <1003;-- 此时触发器之后执行一次

end;

　　触发器触发次序

1、执行BEFORE语句级触发器;
2、对与受语句影响的每一行
2.1、执行BEFORE行级触发器
2.2、执行DML语句
2.3、执行AFTER行级触发器
3、执行AFTER语句级触发器

三、替代触发器

用于执行一个替代操作来代替触发事件的操作。例如：针对INSERT事件的替代触发器，它由INSERT语句触发，当出现INSERT语句时，该语句不会被执行，而是执行替代触发器中定义的语句。
创建INSTEADOF触发器需要注意以下几点：

只能被创建在视图上，并且该视图没有指定WITHCHECKOPTION选项。
不能指定BEFORE或AFTER选项。FOREACHROW子可是可选的，即INSTEADOF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
没有必要在针对一个表的视图上创建INSTEADOF触发器，只要创建DML触发器就可以了.

　　示例代码：

-- 视图

create or replace view v_person as

  select username, age, password,  id p_id from person;

-- 触发器

create or replace trigger update_v_person INSTEAD OF

update on v_person

begin

  update person set

    username = substr(:new.username, instr(:new.username,',')+2),

    age = :new.age,

    password = :new.password

  where id = :new.p_id; -- 视图中p_id字段

end update_v_person; 

-- 尝试更新name

update person set username = 'Chen1, Donny1', age =18 where id=1

select * from person where id = 1;

四、系统触发器
系统事件触发器在发生如数据库启动或者关闭等系统事件时触发，包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。系统触发器可以在DDL语句（数据库定义语句，如crate，alter，drop等）事件来触发。

系统触发器的时间和运行时机如下：

事件	运行启动的时机	说明
启动（startup）	之后	实例启动时激活
关闭（shutdown）	之前	实例正常关闭时激活
服务器错误（servererror）	之后	只要有错误就激活
登录（login）	之后	成功登录后激活
注销（logoff）	之前	开始注销时激活
创建（create）	之前，之后	在创建之前或之后激活
删除（delete）	之前，之后	在撤销之前或之后激活
修改（alter）	之前，之后	在变更之前或之后激活

　　示例

　　记录用户登录信息

-- 准备表登录后保存用户登录信息

create table loggin_event(

    username   varchar2(50),

    logintime  date

);

-- 登录触发器

create or replace trigger t_login

   after LOGON ON DATABASE

declare

begin

  -- 保存用户登录信息

  insert into loggin_event(username, logintime)

  values (USER, sysdate);

  commit;

end t_login;

　　记录创建表的信息

-- 记录创建表的信息

drop table test_create_log;

create table test_create_log(

     event  varchar2(50),

     type varchar2(50),

     name varchar2(50),

     owner varchar2(50),

     createtime date  default sysdate

);

-- 创建表的时候记录

create or replace trigger t_create

   after create on database

declare

   v_event varchar2(50);

   v_type varchar2(50);

   v_name varchar2(50);

   v_owner varchar2(50);

begin

  -- 读取DDL事件属性

   v_event := sysevent; --事件

   v_type := dictionary_obj_type; -- 对象类型，如表

   v_name := dictionary_obj_name; -- 对象名称；如表没

   v_owner := dictionary_obj_owner; -- 拥有者

   insert into test_create_log values(v_event, v_type, v_name, v_owner, sysdate);

end t_create;   

select * from test_create_log;

drop table t_1;

create table t_1(id number);

select * from test_create_log;

四、触发器需要注意的地方

　　before和afterz是支持触发器的触发时间，是前触发还是后触发。

　　for each row 说名触发器是行触发器。当圣灵for each row语句时，触发器为语句触发器，替代触发器（instead of ）为行触发器。

　　feferencing 子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称，参照当前的新、旧列值，默认的名称成为old和new。触发器的PL/SQL块中应相关名称时，必须在他们之前加冒号（:），但在when子句中则不能加冒号。

　　:new 访问操作完成后的值，:old访问操作完成前的值。

　　　　对于insert 只有:new有效,对于update :new、:old都有效，对于delete只有:old有效。

　　oracle触发器的应用一般应用在以下方面：

　　1、数据库的安全性
　　　　可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。可以基于数据库中的数据限制用户的操作，例如不允许价格的升幅一次超过10%。

　　2、实施复杂的安全性授权
　　　　利用触发器控制实体的安全性，可以将权限藉于各种数据库的值。

　　3、提供复杂的审计功能
　　　　审计用户操作数据库的语句。把用户对数据库的更新写入审计表。

　　4、维护不同数据库之间同步表
　　　　在不同的数据库之间可以利用快照来实现数据的复制，但有些系统(例如某个系统有两个数据库，一个只提供系统读，一个值提供系统写)要求两个数据库数据
实时同步，就必须利用触发器从一个数据库中向另一个数据库复制数据。

　　5、实现复杂的数据完整性规则
　　　　实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不
同，触发器可以引用列或数据库对象。提供可变的缺省值。

　　6、实现复杂的非标准的数据库相关完整性规则
　　　　触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的
删除触发器可导致相应删除在其它表中的与之匹配的行。触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。

　　7、可提供表的同步复制

　　8、事件日志记录

巴特西

PL/SQL之--触发器

最新文章

热门文章