orcle触发器

触发器

一、触发器概述
触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发 
当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码 
二、触发器类型
DML触发器 
在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。
替代触发器
oracle8专门为进行视图操作的一种触发器
系统触发器 
对数据库系统事件进行触发，如启动、关闭等
三、触发器组成
触发事件 
DML或DDL语句。
触发时间
是在触发事件发生之前(before)还是之后(after)触发
触发操作
使用PL/SQL块进行相应的数据库操作
触发对象
表、视图、模式、数据库
触发频率 
触发器内定义的动作被执行的次数。
四、触发器由触发事件、触发条件和触发操作3个部分组成 
编写触发器执行代码时，需要注意以下限制 ：
触发器不接受参数 
一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。 
一个表上的触发器越多，该表上的DML操作的性能影响就越大 
触发器代码的大小不能超过32K。如需要大量的代码创建触发器，则首先创建过程，然后在触发器中使用CALL语句调用过程 
触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句，
不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）
五、创建触发器
语句触发器 
语句触发器是指当执行DML语句时被隐含执行的触发器 
如果在表上针对某种DML操作创建了语句触发器，则当执行DML操作时会自动地执行触发器的相应代码 
为了审计DML操作，或者确保DML操作安全执行时，可以使用语句触发器
创建触发器基本语法:
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}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name; 
参数说明:
trigger_name:指定触发器名
BEFORE | AFTER: 指定触发时机（BEFORE或AFTER）
event : 指定触发事件（INSERT、UPDATTE和DELETE）
table_name: 指定DML操作所对应的表名 
FOR EACH ROW: 说明触发器为行触发器 
REFERENCING : 说明相关名称 
WHEN: 说明触发约束条件
六、触发语句与伪记录变量的值
触发语句         :old                       :new
Insert                                 将要插入的数据
Update       更新以前该行的值         更新后的值
Delete        删除以前该行的值
七、触发器的触发次序
1. 执行 BEFORE语句级触发器;
2. 对与受语句影响的每一行：
执行 BEFORE行级触发器
执行 DML语句
执行 AFTER行级触发器 
3. 执行 AFTER语句级触发器
八、创建DML触发器
1.创建一个student_log日志表
create table student_log as select * from student where 1=2;
alter table student_log add(todo nvarchar2(20));
2.创建一个删除学生表的触发器
create or replace trigger tr_del_stu
before delete
on student
for each row
begin
  insert into student_log sl(sl.stu_id,sl.stu_name,sl.class_id,sl.sex,sl.email,sl.address,sl.todo)
  values(:old.stu_id,:old.stu_name,:old.class_id,:old.sex,:old.email,:old.address,'删除');  
 end;  
   
   
select * from student;   
select * from student_log;   
delete from student stu where stu.stu_id='&no' 
/*
3.创建一个新增学生表的触发器
*/
create or replace trigger tr_ins_stu
after insert
on student
for each row
  begin
     insert into student_log sl(sl.stu_id,sl.stu_name,sl.class_id,sl.sex,sl.email,sl.address,sl.todo)
  values(:new.stu_id,:new.stu_name,:new.class_id,:new.sex,:new.email,:new.address,'新增');  
end;

/*
4.创建一个修改学生表的触发器
*/
create or replace trigger tr_update_stu
after update
on student
for each row
  begin
     insert into student_log sl(sl.stu_id,sl.stu_name,sl.class_id,sl.sex,sl.email,sl.address,sl.todo)
  values(:old.stu_id,:old.stu_name,:old.class_id,:old.sex,:old.email,:old.address,'修改前'); 
  insert into student_log sl(sl.stu_id,sl.stu_name,sl.class_id,sl.sex,sl.email,sl.address,sl.todo)
  values(:new.stu_id,:new.stu_name,:new.class_id,:new.sex,:new.email,:new.address,'修改后'); 
end;
 insert into student sl(sl.stu_id,sl.stu_name,sl.class_id,sl.sex,sl.email,sl.address)
  values(1014,'刘德华',1,'男','ldh@163.com','中国香港');  
update student stu set stu.stu_name='陈小春'   where  stu.stu_id=1014;
5.创建BEFORE语句触发器 
如果指定了BEFORE关键字，则表示执行DML操作之前触发触发器
create or replace trigger tri_no_sun
before 
insert or update or delete
on emp
begin
  if to_char(sysdate,'day')in('星期四','星期六') then
  raise_application_error(-20000,'不能在周四或周六修改员工信息');
  end if;
end;
6.使用条件谓词 
当在触发器中同时包含多个触发事件时，为了在触发器代码中区分具体的触发事件，可以使用以下3个条件谓词 
INSERTING：当触发事件是INSERT操作时，该条件谓词返回值为TRUE，否则返回FALSE
UPDATING：当触发事件是UPDATE操作时，该条件谓词返回值为TRUE，否则返回FALSE
DELETING：当触发事件是DELETE操作时，该条件谓词返回值为TRUE，否则返回FALSE
一次创建多个操作触发器，并且使用条件谓词:
create or replace trigger tr_oppen_student
  before insert or update or delete on student
  for each row
begin
  case
    when inserting then
      insert into student_log sl
        (sl.stu_id,
         sl.stu_name,
         sl.class_id,
         sl.sex,
         sl.email,
         sl.address,
         sl.todo)
      values
        (:new.stu_id,
         :new.stu_name,
         :new.class_id,
         :new.sex,
         :new.email,
         :new.address,
         '新增');
     
        when updating then
          insert into student_log sl
            (sl.stu_id,
             sl.stu_name,
             sl.class_id,
             sl.sex,
             sl.email,
             sl.address,
             sl.todo)
          values
            (:old.stu_id,
             :old.stu_name,
             :old.class_id,
             :old.sex,
             :old.email,
             :old.address,
             '修改前');
          insert into student_log sl
            (sl.stu_id,
             sl.stu_name,
             sl.class_id,
             sl.sex,
             sl.email,
             sl.address,
             sl.todo)
          values
            (:new.stu_id,
             :new.stu_name,
             :new.class_id,
             :new.sex,
             :new.email,
             :new.address,
             '修改后');
           when deleting then
              insert into student_log sl
                (sl.stu_id,
                 sl.stu_name,
                 sl.class_id,
                 sl.sex,
                 sl.email,
                 sl.address,
                 sl.todo)
              values
                (:old.stu_id,
                 :old.stu_name,
                 :old.class_id,
                 :old.sex,
                 :old.email,
                 :old.address,
                 '删除');
          end case;
      end;
7.行级触发器 
for each row
:old  修改前的该行记录
:new  修改后的该行记录
如果修改的是部门编号为30的员工工资，则工资不能降低
也可以使用“UPDATING('列名')”的语法进一步判断某个列是否被更新了。例如，以下代码表示如果表emp的sal列更新了，则执行某种处理：
IF UPDATING('sal')   THEN
       ...
END IF;
例如:
create or replace trigger trig_update_sal
before update of sal,comm
or delete on emp
for each row
when(old.deptno=30)   --- old：删除或修改前的数据。
begin
  case 
    when updating('sal') then    ---:new：修改后的数据
      if :new.sal<:old.sal then   --- :old：删除或修改前的数据:new：修改后的数据 
        raise_application_error(-20002,'部门30的人员工资不能降');
      end if;
    when updating('comm') then
      if :new.comm<:old.comm then
        raise_application_error(-20001,'部门30的奖金不能降低！');
      end if;
    when deleting then
      raise_application_error(-20003,'不能删除部门30的员工');
  end case;
end;
8.创建AFTER语句触发器  
如果指定了AFTER关键字，则表示在执行DML操作之后触发触发器
利用行触发器实现级联更新。在修改了主表dept中的deptno之后（AFTER），级联的、自动的更新子表emp中原来在该部门的deptno。 
create or replace trigger tri_casupdate
after update of deptno on dept    ----在部门表的deptno字段建立after update触发器。
for each row
begin
  dbms_output.PUT_LINE('旧的deptno值是：'||:old.deptno);
  dbms_output.PUT_LINE('新的deptno值是：'||:new.deptno);
  update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
9.在触发器中调用存储过程  
有时业务逻辑过于复杂，触发器内容有限（32K)，只能借助于存储过程
在删除dept表中记录时，将原有的记录保存到一个回收表delDept中
创建delDept表
create table delDept(
  	deptno number(7) ,
  	dname varchar2(30),
  	loc varchar2(40)
);
 
创建添加数据的存储过程
create or replace procedure 
pro_addOldDept(dno number,dname varchar2,loc varchar2)
is
begin
  insert into delDept values(dno,dname,loc);
end;
触发器中调用存储过程
create  or replace trigger tri_pro
after delete on dept
for each row
begin
  pro_addOldDept(:old.deptno,:old.dname,:old.loc);
end;
10.创建INSTEAD OF 触发器
为了在不能执行DML操作的复杂视图上执行DML操作，必须基于视图创建INSTEAD OF触发器
创建INSTEAD OF触发器时需要注意以下几点 
INSTEAD OF选项只适用于视图
当基于视图创建触发器时，不能指定BEFORE和AFTER选项
当创建INSTEAD OF触发器时，必须指定FOR EACH ROW选项
只能在视图上创建INSTEAD OF触发器，而不能创建其他类型的触发器
基本语法:
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name; 
创建查询视图
create or replace view emp_view as
select deptno,count(*) total_employeer,sum(sal) total_sal
from emp group by deptno;
创建instead_of触发器
create or replace trigger emp_view_del
instead of delete on emp_view for each row
begin
  delete from emp where deptno=:old.deptno;
end;
如果执行delete from emp_view where deptno=10 ;
11.创建系统事件触发器
系统事件触发器是指基于Oracle系统事件所创建的触发器 
创建系统事件触发器时，应用开发人员经常需要使用事件属性函数
事件属性函数                          功能 
ora_client_ip_address             返回客户端的IP地址 
ora_database_name               返回当前数据库名 
ora_dict_obj_name               返回DDL操作所对应的数据库对象名 
ora_dict_obj_owner              返回DDL操作所对应的对象的所有者名 
ora_dict_obj_type                返回DDL操作对应的数据库对象的类型 
ora_instance_num                              返回例程号 
ora_is_alter_column(column_name IN VARCHAR2)    检测特定列是否被修改 
ora_is_drop_column(column_name IN VARCHAR2)    检测特定列是否被删除 
ora_login_user                                  返回登录用户名 
ora_sysevent                                    返回触发触发器的系统事件名
创建登录和退出触发器 
为了记载用户的登录和退出事件，可以分别创建登录和退出触发器
创建用于存放登录和退出信息的表
创建登录和退出触发器
用户登录数据库或断开与数据库的连接时执行相应的触发器代码
create table log_event(
  username varchar2(20),
  ipAddress varchar2(20),
  logonTime timestamp,
  logoffTime timestamp
);
create or replace trigger logon_trigger
after logon on database
begin
  insert into log_event(username,ipaddress,logonTime)
  values(ora_login_user,ora_client_ip_address,sysdate);
end;
create or replace trigger logoff_trigger
before logoff on database
begin
  insert into log_event(username,ipaddress,logoffTime)
  values(ora_login_user,ora_client_ip_address,sysdate);
end;
12.管理触发器
显示触发器信息 
通过查询数据字典视图USER_TRIGGERS，可以显示当前用户所包含的所有触发器信息
禁用或启用触发器
重新编译触发器
当使用ALTER TABLE命令修改表的结构时，会使触发器变为无效状态
为了使触发器继续生效，需要重新编译触发器    
删除触发器