16_pl_trigger.txt

假期oracle数据库培训记录的一部分,其他人不需帐号就可自由下载此源码

--trigger不能含有事物处理的语句，除非使用间接的自动提交。
例题一
/*  更新D表的DEPTNO时自动更新E表的DEPTNO  */
connect scott/tiger
drop table d;
drop table e;
create table d as select * from dept;
create table e as select * from emp;

drop trigger d_update;

CREATE or replace TRIGGER d_update
AFTER delete or UPDATE OF deptno ON d
FOR EACH ROW 
BEGIN
--当D表的部门号修改的时候E表的对应部门号也相应的修改
IF  (UPDATING AND :old.deptno != :new.deptno)
THEN UPDATE e
SET deptno = :new.deptno
WHERE deptno = :old.deptno;
END IF;
--当D表的某个部门号删除的时候，E表的对应部门同时被删除
if deleting then
delete e where deptno=:old.deptno;
end if;
END;
/
select * from e;
select * from d;
update d set deptno=50 where deptno=30;
select * from e;
select * from d;
delete d where deptno=20;
----------------------------------------------------------------------------

例题二
--禁止对表E的SAL列进行修改
create or replace trigger e_update
before update of sal on e
begin
if updating then
raise_application_error(-20001,'工资不能被改动');
end if;
end;
/
-----------------------------------------------------------------------------
例题三
--保存老值和新的值
CONNECT SCOTT/TIGER

DROP TABLE T1;
CREATE TABLE T1 AS SELECT SAL OLD_VALUE,SAL NEW_VALUE FROM EMP WHERE 0=9;

CREATE OR REPLACE TRIGGER TRG1
BEFORE INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
INSERT INTO T1 VALUES(:OLD.SAL,:NEW.SAL);
END;
/

SELECT * FROM T1;
update emp set sal=sal+1;
commit;
select * from t1;
----------------------------------------------------------------------------
例题四
--建立一个不可修改的视图V1
drop table e1;
create table e1 as select * from emp;
drop view v1;
create view v1 as
select distinct deptno from e1;
--试图修改V1时报错
update v1 set deptno=50 where deptno=10;
--建立一个替代触发器，当修改V1的时候会自动的修改基表
create or replace trigger trigger_instead_of
instead of insert or update or delete on v1
for each row
begin
if updating then
update e1 set deptno=:new.deptno where deptno=:old.deptno;
end if;
end;
/
--验证触发器起作用
update v1 set deptno=50 where deptno=10;
select * from e1;
commit;

---------------------------------------------------------------
--验证触发器的状态
select trigger_name,status from user_triggers;
--改变触发器的状态
--禁用某个触发器
ALTER TRIGGER trigger_instead_of disable;
--禁用某个表上的所有触发器
alter table e disable all triggers;
--删除触发器
DROP TRIGGER trigger_instead_of ;