注意：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;
?