触发器整理

一、概述

1.1 触发器概述**

触发器是在事件发生时隐式地自动运行的PL/SQL程序块，不能接收参数，不能被调用。它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如:当对一个表进行操作（ insert，delete， update）时就可以激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器是许多关系数据库系统都提供的一项技术。

触发器与储存过程:

都有声明，执行和异常处理过程的PL/SQL块。触发器可以调用存储过程。我的一般理解就是触发器是一个隐藏的存储过程，因为它不需要参数，不需要显示调用。**

1.2 触发器组成:

触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。既可以是单个触发事件，也可以是多个触发事件的组合（多个触发事件组合只能使用OR逻辑组合，不能使用AND逻辑组合）。

触发时间：即该触发器在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该触发器的操作顺序。触发操作：即该触发器被触发之后的操作，正是触发器本身要做的事情。 例如：PL/SQL 块。触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。  

• INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。  
• UPDATING*[（*column_1,column_2,…,column_x）]：当触发事件是UPDATE时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。  
• DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表

**1.3 触发器触发次序**

1.执行 BEFORE语句级触发器;

2.对与受语句影响的每一行：  

执行 BEFORE行级触发器  

执行 DML语句  

执行 AFTER行级触发器 

3.执行 AFTER语句级触发器

二、语法

2.1 触发器创建语法

创建触发器的一般语法是:

CREATE [OR REPLACE] TRIGGER trigger_name  

TRIGGERING_EVENT

{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;  

TRIGGERING_EVENT表示激发触发器的事件（也可能包括特殊的表或视图），即BEFORE、AFTER、INSTEAD OF。

BEFORE和AFTER指出触发器的触发时序分别为前触发和后触发方式；前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。  

INSTEAD OF 触发器则只能为行触发器。 INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。创建INSTEAD OF触发器需要注意以下几点：

1. (1) 只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
2. (2) 不能指定BEFORE 或 AFTER选项。
3. (3) FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
4. (4) 没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。  

FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，AFTER和BEFORE触发器为语句触发器  

REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。  

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用INSTEAD OF 行触发器和其它类型的触发器中。当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

2.2删除、禁用或启用触发器

删除触发器：

drop trigger trigger_name;

当删除其他用户模式中的触发器名称，需要具有drop any trigger 系统权限，当删除建立在数据库上的触发器时，用户需要具有administer database trigger 系统权限。此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。

禁用或启用触发器：

数据库trigger 的状态：

有效状态(enable)：当触发事件发生时，处于有效状态的数据库触发器trigger 将被触发。

无效状态(disable)：当触发事件发生时，处于无效状态的数据库触发器trigger 将不会被触发，此时就跟没有这个数据库触发器(trigger) 一样。

数据库trigger 的这两种状态可以互相转换。格式为：alter trigger trigger_name [disable | enable ];

例：alter trigger *emp_view_delete* disable;  

alter trigger语句一次只能改变一个触发器的状态，而alter table语句则一次能够改变与指定表相关的所有触发器的使用状态。格式为：  

alter table [schema.]table_name {enable|disable} all triggers;

例：使表EMP 上的所有TRIGGER 失效：

alter table *emp* disable all triggers;

三、触发器分类

触发器必须由事件才能触发。触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。 每类事件包含若干个事件，如下所示。数据库的事件是具体的，在创建触发器时要指明触发的事件。 

3.1 DML事件(3种)

INSERT 在表或视图中插入数据时触发 

UPDATE 修改表或视图中的数据时触发 

DELETE 在删除表或视图中的数据时触发  

3.2 DDL事件(3种)

CREATE 在创建新对象时触发 

ALTER 修改数据库或数据库对象时触发 

DROP 删除对象时触发

Oracle10g提供的系统事件触发器可以在DDL或数据库系统上被触发。

DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。

而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。

下面给出系统触发器的种类和事件出现的时机（前或后）：

事件	允许的时机	说明
STARTUP	AFTER	启动数据库实例之后触发
SHUTDOWN	BEFORE	关闭数据库实例之前触发（非正常关闭不触发）
SERVERERROR	AFTER	数据库服务器发生错误之后触发
LOGON	AFTER	成功登录连接到数据库后触发
LOGOFF	BEFORE	开始断开数据库连接之前触发
CREATE	BEFORE，AFTER	在执行CREATE语句创建数据库对象之前、之后触发
DROP	BEFORE，AFTER	在执行DROP语句删除数据库对象之前、之后触发
ALTER	BEFORE，AFTER	在执行ALTER语句更新数据库对象之前、之后触发
DDL	BEFORE，AFTER	在执行大多数DDL语句之前、之后触发
GRANT	BEFORE，AFTER	执行GRANT语句授予权限之前、之后触发
REVOKE	BEFORE，AFTER	执行REVOKE语句收权限之前、之后触犯发
RENAME	BEFORE，AFTER	执行RENAME语句更改数据库对象名称之前、之后触犯发
AUDIT / NOAUDIT	BEFORE，AFTER	执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

3.3数据库事件(5种)  

STARTUP 数据打开时触发  

SHUTDOWN 在使用NORMAL或IMMEDIATE选项关闭数据库时触发 

LOGON 当用户连接到数据库并建立会话时触发 

LOGOFF 当一个会话从数据库中断开时触发 

SERVERERROR 发生服务器错误时触发  

四、 DML触发器的限制

(1) 触发器不接受参数。

(2) 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

(3) 触发器最大为32KB。(4) 若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

(5) 触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句。

(6) 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

(7) 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

(8) 在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。

(9) 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

五、触发器应用分析

触发器是维护数据库数据完整性的重要技术,通过对DBMS个性的分析,可以发现触发器使用的普遍规律,触发器通过对新旧数据更换的管理,可以保留历史性数据档案.数据库触发器应用技巧的好坏决定了应用程序的质量和效率.例如,对DBMS的维护技术和安全监控技术可以进行有益的补充和最直接的反映.

在设计数据库的时候也都会或多或少的使用存储过程和触发器，原因很简单：良好的性能，业务也好实现。

触发器能实现如下功能：

1、 允许/限制对表的修改

2、 自动生成派生列，比如自增字段

3、 强制数据一致

4、 提供审计和日志记录

5、 防止无效的事务处理

6、 启用复杂的业务逻辑

六、示例

例(一)

限定不能对部门号为30的记录进行 行触发器操作。

--建立一个触发器, 当职工表 emp2 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

create table emp2 as select * from emp;

create table emp_his as select * from emp2 where 1=2;

create or replace trigger tri_del_emp
   before delete --指定触发时机为删除操作前触发
   on scott.emp2 
   for each row   --说明创建的是行级触发器 
begin
   --将修改前数据插入到日志记录表 emp_his
   insert into emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       values( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
end;

select * from emp_his;
delete emp2 where empno=7788;

drop trigger tri_del_emp;
drop table emp2;
drop table emp_his;

例(二)

限定不能对部门号为30的记录进行 行触发器操作。

--限定不能对部门号为30的记录进行 行触发器操作。
create or replace trigger tri_limit_emp
before update or insert or delete  --update、insert、delete 后面可以跟列名  update of empno,ename or delete...
on scott.emp
for each row
when (old.deptno=30 or new.deptno=30)  
begin   
    case         
         when updating then
              raise_application_error(-20001,'不能修改emp表中，部门为30的员工信息');
         when deleting then
              raise_application_error(-20002,'不能删除emp表中，部门为30的员工信息');
         when inserting then
              raise_application_error(-20003,'不能向30号部门添加员工信息');
    end case;
end;

select * from emp;
delete from emp where deptno=30;
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)values(1212,'name','job',7839,sysdate,450.00,200.00,30);
update emp set job='CLERK' where deptno=30;
drop trigger tri_limit_emp;

例(三)

当对tb_user表执行DML语句时，将相关信息记录到日志表

--当对tb_user表执行DML语句时，将相关信息记录到日志表
--创建测试表
create table tb_user(
  u_id  number(5),
  u_name varchar2(40),
  u_age number(2),
  u_sex char(2)
);
--创建记录测试表
create table user_log(
  ul_user  varchar2(50),
  ul_type  varchar2(50),
  ul_date  varchar2(50)
);
 
--创建触发器
create or replace trigger tri_user
  after delete or insert or update
  on scott.tb_user
declare
  v_type user_log.ul_type%type;
begin
  if inserting then
    v_type := 'insert';
    Dbms_Output.put_line('记录已经成功插入，并已记录到日志');
  elsif updating then
    v_type := 'update';
    Dbms_Output.put_line('记录已经成功更新，并已记录到日志');
  elsif deleting then
    v_type := 'delete';
    Dbms_Output.put_line('记录已经成功删除，并已记录到日志');
  end if;
  insert into user_log(ul_user,ul_type,ul_date) values(user, v_type, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); --USER表示当前用户名
end;

insert into tb_user(u_id,u_name,u_age,u_sex) values(7788,'张三',23,'男');
commit;
update tb_user set u_age = 30 where u_id = 7788;
commit;
delete tb_user where u_id = 7788;
commit;

select * from tb_user;
select * from user_log;
 
drop trigger tri_user;
drop table user_log;
drop table tb_user;

例(四)

限制对empc表修改（包括insert,delete,update）的时间范围，即不允许在非工作时间修改empc表

--限制对empc表修改（包括insert,delete,update）的时间范围，即不允许在非工作时间修改empc表。
create or replace trigger tri_disable_use_emp
before insert or update or delete
on scott.emp
begin        
    if (to_char(sysdate,'DAY') in ('星期六', '星期日')) or (to_char(sysdate, 'HH24:MI') not between '08:30' and '18:00') then      
      raise_application_error(-20001, '不是上班时间，不能修改emp表');      
    end if;
end;
delete emp where empno=7369;
drop trigger tri_disable_use_emp;

例(五)

利用行触发器实现级联更新

--利用行触发器实现级联更新。在修改了主表dept中的deptno之后（after），级联的、自动的更新子表emp表中empno。
create table deptc as select * from dept;
create table empc as select * from emp;
alter table deptc add constraint pk_deptc primary key (deptno);
alter table empc add constraint pk_empc primary key(empno);
alter table empc add constraint fk_empc foreign key (deptno) references deptc ( deptno );

create or replace trigger tri_ref_deptc
after update of deptno
on scott.deptc
for each row
begin  
    dbms_output.put_line('旧的deptno值是'||:old.deptno||'、新的deptno值是'||:new.deptno);   
    update empc set deptno=:new.deptno where deptno=:old.deptno;
end;

update deptc set deptno=22 where deptno=20;

select * from deptc;
select * from empc;

alter table empc drop constraint fk_empc;
drop table deptc;
drop table empc;

例(六)

在触发器中调用储存过程

--在触发器中调用储存过程
--员工离职记录
create table empc as select * from emp;
create table emp_leave_record as select * from emp where 1=2;

--创建储存过程
create or replace procedure pro_leave_record( 
       v_empno emp_leave_record.empno%type,
       v_ename emp_leave_record.ename%type,
       v_job emp_leave_record.job%type,
       v_mgr emp_leave_record.mgr%type,
       v_hiredate emp_leave_record.hiredate%type,
       v_sal emp_leave_record.sal%type,
       v_comm emp_leave_record.comm%type,
       v_deptno emp_leave_record.deptno%type
)
as
begin        
       insert into emp_leave_record (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);
end;

--创建触发器
create or replace trigger tri_pro   
before delete 
on scott.empc
for each row
begin   
   pro_leave_record(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;

delete empc where empno=7788;
commit;

delete empc where deptno=30;
commit;

select * from emp_leave_record;
select * from empc;

drop trigger tri_pro; 
drop procedure pro_leave_record;
drop table emp_leave_record;
drop table empc;

例(七)

使用触发器实现序号自增长

--使用触发器实现序号自增长
--创建一个测试表：
create table tb_user(
  id number(11) primary key,
  username varchar(50),
  password varchar(250)
);
--创建一个序列：
create sequence seq_user increment by 1 start with 1;
--创建一个触发器：  
create or replace trigger tri_seq_user
before insert
on tb_user
for each row
declare
    next_id number;
begin     
    select seq_user.nextval into next_id from dual;
    :new.id := next_id; --:new 表示新插入的那条记录  把next_id赋值给新插入的那条记录的id
end;

--向表插入数据:
insert into tb_user(username,password) values('张三','123');
insert into tb_user(username,password) values('admin','hgr');
commit;
 
select * from tb_user;

drop trigger tri_seq_user; 
drop sequence seq_user; 
drop table tb_user;

例(八)

创建触发器，利用视图插入数据

--insert of
--创建触发器，利用视图插入数据
--创建表
create table tb_user (
       u_id number(4) primary key,
       u_name varchar2(40) not null,
       u_sex char(2),
       u_age number(2)
);
create table user_info(
       u_id number(4) primary key,
       u_tel varchar2(40) not null,
       u_card varchar2(20)
);
--插入数据
insert into tb_user (u_id,u_name,u_sex,u_age) values (1001,'张三','男',23);
insert into tb_user (u_id,u_name,u_sex,u_age) values (1002,'李四','男',35);
insert into tb_user (u_id,u_name,u_sex,u_age) values (1003,'王五','女',27);
commit;
insert into user_info (u_id,u_tel,u_card) values(1001,'15911122234','500222111105223344');
insert into user_info (u_id,u_tel,u_card) values(1002,'13466633367','500222112343677654');
insert into user_info (u_id,u_tel,u_card) values(1003,'15823457643','500222786743343544');
commit;
--创建视图连接两张表

create or replace force view view_user
as
select u.u_id,u.u_name,u.u_sex,u.u_age,ui.u_tel,ui.u_card from tb_user u,user_info ui where u.u_id=ui.u_id;
--创建触发器
create or replace trigger tri_user
 instead of insert
 on view_user
begin
 insert into tb_user (u_id,u_name,u_sex,u_age) values (:new.u_id,:new.u_name,:new.u_sex,:new.u_age);
 insert into user_info (u_id,u_tel,u_card) values(:new.u_id,:new.u_tel,:new.u_card);
end;

--利用视图插入数据
insert into view_user (u_id,u_name,u_sex,u_age,u_tel,u_card) values (1006,'admin','男',23,'1345228767','500222109497863245');
commit;

select * from view_user;
select * from tb_user;
select * from user_info;

drop trigger tri_user;
drop view view_user;
drop table tb_user;
drop table user_info;

例(九)

创建登录、退出触发器

--DDL事件   
  

--创建登录、退出触发器。
 
create table log_login(
 id number(5)primary key,
 username varchar2(10),
 address varchar2(20), 
 login_date timestamp,
 exit_date timestamp
); 

create sequence seq_login increment by 1 start with 1;
--创建登录触发器
create or replace trigger tri_login
after logon  
on database
begin 
   insert into log_login (id,username, address, login_date) values (seq_login.nextval,ora_login_user, ora_client_ip_address, systimestamp);
end tr_login;
--创建退出触发器
create or replace trigger tri_exit
before logoff
on database
begin
   insert into log_event (id,user_name, address, exit_date) values (seq_login.nextval,ora_login_user, ora_client_ip_address, systimestamp);
end tri_exit;
    
select * from log_login;

drop trigger tri_login;
drop trigger tri_exit;
drop sequence seq_login;
drop table log_login;    

例(十)

创建触发器，将操作CREATE、DROP记录在log_info表

--创建触发器，将操作CREATE、DROP记录在log_info表
--创建表
create table log_info(
  l_user varchar2(50),
  l_date varchar2(50),
  l_name varchar2(50),
  l_username   varchar2(50),
  l_type   varchar2(50)
);

--创建触发器
create or replace trigger tri_log
 after create or drop 
 on schema
begin
 insert into log_info
 values
  (user,sysdate,
   sys.dictionary_obj_name,
   sys.dictionary_obj_owner,
   sys.dictionary_obj_type);
end;

create table tb_test(id number);
drop table tb_test;

select * from log_info;

drop trigger tri_log;
drop table log_info;