第十五章 触发器

[sql]代码库

第十五章 触发器

15.1分类：
DML触发器-----------基于表的（insert、alter、update）
替代触发器-----------基于VIEW的
系统触发器-----------基于系统的

好处：自动调用、记录日志、保证数据安全、用数据库触发器可以保证数据的一致性和完整性。

语法：
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| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;
       
       BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。
       FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。
       REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。
      WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

15.2、 触发器触发次序

1.        执行 BEFORE语句级触发器;
2.        对与受语句影响的每一行：
l         执行 BEFORE行级触发器
l         执行 DML语句
l         执行 AFTER行级触发器 
3.        执行 AFTER语句级触发器

15.3、语句触发器
after 语句触发器
Before 语句触发器
例如：禁止工作人员在休息日改变雇员信息
create or replace trigger tr_src_emp
before insert or update or delete 
on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') in( 'SAT','SUN') then 
raise_application_error(-20001,'can‟t modify user information in weekend'); 
end if; 
end;
/
 
使用条件谓语---------inserting、updating、deleting
 create or replace trigger tr_src_emp 
before insert or update or delete 
on emp 
begin
if to_char(sysdate,'DY') in( '星期六','星期天') then 
case 
when inserting then
  raise_application_error(-20001,'fail to insert');  
when updating then
  raise_application_error(-20001,'fail to update'); 
 when deleting then
  raise_application_error(-20001,'fail to delete'); 
end case; 
end if; 
end;
/
15.4、行触发器
执行 DML 操作时，每作用一行就触发一次触发器。
Bofre 行触发器
例如：确保员工工资不能低于原有工资
Create or replace trigger tr_emp_sal 
before update of sal 
on emp 
for each row 
begin
if :new.sal<:old.sal then
 raise_application_error(-20010,'sal should not be less'); 
end if; 
end;
/
after 行触发器
例如：统计员工工资变化
Create table audit_emp_change(
Name varchar2(10),
Oldsal number(6,2),
Newsal number(6,2),
Time date);
Create or replace trigger tr_sal_sal 
after update of sal 
on emp 
for each row 
declare 
v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename; if v_temp=0 then 
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate); else
 update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate  where name=:old.ename;
end if;
 end;
/
限制行触发器 
Create or replace trigger tr_sal_sal 
after update of sal 
on emp 
for each row when (old.job=‟SALESMAN‟) 
declare 
v_temp int; 
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename; 
if v_temp=0 then 
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate); 
else
 update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate  where name=:old.ename;
end if; 
end;
/

注意：
例如：如果要基于 EMP 表建立触发器。那么该触发器的执行代码不能包含对 EMP 表的查询操作编写 DML 触发器的时，触发器代码不能从触发器所对应的基表中读取数据。
Create or replace trigger tr_emp_sal 
Before update of sal 
on emp 
For each row  
declare
Maxsal number(6,2);
Begin
If :new.sal>maxsal then Select max(sal) into maxsal from emp;
Raise_application_error(-21000,‟error‟);
End if;
End;
/
 创建的时候不会报错。但是一旦执行就报错了
update emp set sal=sal*1.1 where deptno=30

DML触发器的限制
l         CREATE TRIGGER语句文本的字符长度不能超过32KB；
l         触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT语句。
l         触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；
触发器不能对多表有效
l         由触发器所调用的过程或函数也不能使用数据库事务控制语句；
l         触发器中不能使用LONG, LONG RAW 类型；
l         触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据；
 
 :NEW 修饰符访问操作完成后列的值
 :OLD 修饰符访问操作完成前列的值 
特性
INSERT
UPDATE
DELETE
OLD
NULL
实际值
实际值
NEW
实际值
实际值
NULL
 
15.4 替代触发器
语法：

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;

例2：创建复杂视图，针对INSERT操作创建INSTEAD OF触发器，向复杂视图插入数据。
l         创建视图：
CREATE OR REPLACE FORCE VIEW "HR"."V_REG_COU" ("R_ID", "R_NAME", "C_ID", "C_NAME")
AS
 SELECT r.region_id,
    r.region_name,
    c.country_id,
    c.country_name
 FROM regions r,
    countries c
 WHERE r.region_id = c.region_id;
 
l         创建触发器：
CREATE OR REPLACE TRIGGER "HR"."TR_I_O_REG_COU" 
INSTEAD OF INSERT 
ON v_reg_cou 
FOR EACH ROW 

DECLARE 
v_count NUMBER;
BEGIN
 SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id;
 IF v_count = 0 THEN
    INSERT INTO regions
      (region_id, region_name
      ) VALUES
      (:new.r_id, :new.r_name
      );
 END IF;

 SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id;
 IF v_count = 0 THEN
    INSERT
    INTO countries
      (
        country_id,
        country_name,
        region_id
      )
      VALUES
      (
        :new.c_id,
        :new.c_name,
        :new.r_id
      );
 END IF;
END;
 
创建INSTEAD OF触发器需要注意以下几点：

l         只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
l         不能指定BEFORE 或 AFTER选项。
l         FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
l         没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。
 
15.5、系统事件触发器
语法：
CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE
{BEFORE|AFTER} 
{ddl_event_list 
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.]SCHEMA }
[WHEN condition]
PL/SQL_block | CALL procedure_name;
其中: ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；
         database_event_list：一个或多个数据库事件，事件间用 OR 分开；

下面给出系统触发器的种类和事件出现的时机（前或后）：
事件
允许的时机
说明
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进行审计或停止审计之前、之后触发

例1：
/*create or replace trigger tr_emp1_1
  --before update on emp1
  after update on emp1
  for each row
declare
  -- local variables here
begin
  if :new.sal<:old.sal then
    Raise_application_error(-20000, ':new.sal>:old.sal');
  end if;
end tr;*/
/*create or replace trigger tr_emp1_2
   after delete on emp1
   --before delete on emp1
   for each row
begin
  Raise_application_error(-20001, '不能删');
end;*/
create or replace trigger tr_emp1_3
after delete or update or insert on emp1
for each ROW
begin
  if deleting then
    if :new.sal>0 then
       Raise_application_error(-20000, 'deleting');
    end IF;
  /*elsif updating then
    if :new.sal<:old.sal then
       Raise_application_error(-20001, 'updating');
    end if;
  else
    if :new.sal<:old.sal then
       Raise_application_error(-20002, 'inserting');
    end if;*/
  end if;
end;

例2：
--替代触发器 

create or replace trigger t_emp1 

  instead OF INSERT OR DELETE OR UPDATE on v_emp1     

declare 

  -- local variables here 

BEGIN 

  IF inserting THEN 

     INSERT INTO dept1 

       (deptno, dname, loc) 

     VALUES 

       (:new.deptno, :new.dname,:new.loc); 

     INSERT INTO emp1 

       (empno, ename, job, mgr, hiredate, sal, comm, deptno) 

     VALUES 

       (:new.empno, :new.ename, :new.job, :new.mgr, :new.hiredate, :new.sal, :new.comm, :new.deptno);      

   ELSIF deleting THEN         

      DELETE FROM emp1 

             WHERE empno=:OLD.EMPNO; 

       DELETE FROM dept1 

              WHERE deptno=:old.DEPTNO;  

     ELSE 

              UPDATE emp1 

                    SET empno=:new.empno,  

                    ename=:new.ename, 

                    job = :new.job,  

                     mgr=:new.mgr, 

                    hiredate = :new.hiredate,  

                    sal= :new.sal, 

                    comm= :new.comm, 

                    deptno= :new.deptno 

                 WHERE :old.empno=:new.empno; 

              UPDATE dept1 

                 SET deptno = :new.deptno, 

                        dname = :new.dname, 

                        loc = :new.loc 

                 WHERE :old.deptno=:new.deptno;   

    END IF;  
end t_emp1;

--系统触发器
CREATE OR REPLACE TRIGGER t_emp1_1
AFTER drop /*OR CREATE OR ALTER */ON c##rain.schema
DECLARE
BEGIN
  INSERT INTO demo VALUES(SYSDATE,USER,ora_dict_obj_name,
                                                      ora_client_ip_address,
                                                      ora_database_name);
END;
调试
DELETE FROM v_emp1 WHERE empno=7369;
SELECT * FROM v_emp1
SELECT * FROM dept1;
SELECT * FROM emp1;
INSERT INTO v_emp1(empno,deptno) VALUES(221,11);
INSERT INTO v_emp1(empno) VALUES(2);
UPDATE v_emp1 SET ename='123' WHERE empno=7893;

SELECT * FROM emp2;
DROP TABLE emp2;
CREATE TABLE a(s NUMBER);
DROP TABLE a;
SELECT * FROM demo;
SELECT * FROM a;
ALTER TABLE demo ADD table_name VARCHAR2(20);
ALTER TABLE demo ADD user_ip NUMBER;
ALTER TABLE demo ADD database_name VARCHAR2(31);
ALTER TABLE demo ADD table_name VARCHAR2(20);
SELECT table_name FROM User_Tables;

资料： 
http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html