PL/SQL复习十三 触发器

注意：1、触发器的大小不能超过32K，如果确实需要建立触发器，应该先建立存储过程，然后在触发器中调用

     2、触发器只能包含select、insert、update和delete语句，而不能包含DDL语句和事务控制语句

     3、触发器代码不能从触发器所对应的基表中读取数据

语句级触发器：

create or replace trigger tr_sec_emp

before insert or update or delete on emp

begin

  if to_char(sysdate,'DY','nls_date_language=AMERICAN') in('SAT','SUN') then

    case

      when inserting then

        raise_application_eroor(-20001,'不能在休息日增加雇员');

      when updating then

        raise_application_error(-20002,'不能在休息日更新雇员');

      when deleting then

        raise_application_eroor(-20003,'不能在休息日删除雇员');

    end case;

  end if;

end;

/

行级触发器：

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,'工资只涨不降');

  end if;

end;

/

限制行级触发器:

create or replace trigger tr_sal_change

after update of sal on emp

for each row

when (old.job = 'SALESMAN')  --用when限制触发条件

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;

/

---------------------------------------------------------------------------------------------

机遇复杂视图的触发器：

grant create view to lixin;--授权建立视图

--建立视图

create or replace view dept_emp as select a.deptno,a.dname,b.empno,b.ename

from dept a, emp b where a.deptno = b.deptno;

--建立触发器：

create or replace trigger tr_instead_of_dept_emp

instead of insert on dept_emp --关键字，只能针对视图

for each row --只能是行级

declare

  v_temp int;

begin

  select count(*) into v_temp from dept where deptno= :new.deptno;

  if v_temp = 0 then

    insert into dept(deptno,dname) values(:new.deptno,:new.dname);

  end if;

  select count(*) into v_temp from emp where empno =:new.empno;

  if v_temp = 0 then

    insert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno);

  end if;

end;

/

---------------------------------------------------------------------------------------------

管理触发器：

查看：select * from user_triggers;

禁用：SQL> alter trigger TR_EMP_SAL disable;

启用：SQL> alter trigger TR_EMP_SAL enable;

禁用表的所有触发器：SQL> alter table emp disable all triggers;

重新编译：SQL> alter trigger TR_EMP_SAL compile;

删除：SQL> drop trigger TR_CHECK_SAL;