当前位置：首页 > Oracle - 把触发器看透
AFTER DELETE ON emp
REFERENCING OLD AS old_emp FOR EACH ROW DECLARE I NUMBER; BEGIN
DBMS_OUTPUT.PUT_LINE(''正在执行trig2_delete 触发器…''); SELECT emp_count INTO I
FROM dept_summary WHERE deptno = :old_emp.deptno; IF I >1 THEN
UPDATE dept_summary
SET sal_sum=sal_sum - :old_emp.sal, Emp_count=emp_count - 1
WHERE deptno = :old_emp.deptno; ELSE
DELETE FROM dept_summary WHERE deptno = :old_emp.deptno; END IF; END;' );
INSERT INTO dept(deptno, dname, loc) VALUES(90, 'demo_dept', 'none_loc'); INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9999, 3000);
INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9998, 2000); DBMS_OUTPUT.PUT_LINE('插入后'); Disp_dept_summary();
UPDATE emp SET sal = sal*1.1 WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('修改后'); Disp_dept_summary();
DELETE FROM emp WHERE deptno=90; DELETE FROM dept WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('删除后'); Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update'); DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert'); DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete'); EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;
例3：利用ORACLE提供的条件谓词INSERTING、UPDATING和DELETING创建与例2具有相同功能的触发器。
BEGIN
DBMS_OUTPUT.PUT_LINE('插入前'); Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2
AFTER INSERT OR DELETE OR UPDATE OF sal ON emp
REFERENCING OLD AS old_emp NEW AS new_emp FOR EACH ROW DECLARE I NUMBER; BEGIN
IF UPDATING AND :old_emp.sal != :new_emp.sal THEN DBMS_OUTPUT.PUT_LINE(''正在执行trig2 触发器…'');
DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); UPDATE dept_summary
SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal WHERE deptno = :new_emp.deptno; ELSIF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); SELECT COUNT(*) INTO I FROM dept_summary
WHERE deptno = :new_emp.deptno; IF I > 0 THEN
UPDATE dept_summary SET sal_sum=sal_sum+:new_emp.sal, Emp_count=emp_count+1 WHERE deptno = :new_emp.deptno; ELSE
INSERT INTO dept_summary
VALUES (:new_emp.deptno, :new_emp.sal, 1); END IF; ELSE
DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); SELECT emp_count INTO I
FROM dept_summary WHERE deptno = :old_emp.deptno;
IF I > 1 THEN
UPDATE dept_summary
SET sal_sum=sal_sum - :old_emp.sal, Emp_count=emp_count - 1
WHERE deptno = :old_emp.deptno; ELSE
DELETE FROM dept_summary WHERE deptno = :old_emp.deptno; END IF; END IF; END;' );
INSERT INTO dept(deptno, dname, loc) VALUES(90, 'demo_dept', 'none_loc'); INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9999, 3000);
INSERT INTO emp(ename, deptno, empno, sal) VALUES(USER, 90, 9998, 2000); DBMS_OUTPUT.PUT_LINE('插入后'); Disp_dept_summary();
UPDATE emp SET sal = sal*1.1 WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('修改后'); Disp_dept_summary();
DELETE FROM emp WHERE deptno=90; DELETE FROM dept WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('删除后'); Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2'); EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;
例4：创建INSTEAD OF 触发器。首先创建一个视图myview, 由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作。
DECLARE
No NUMBER;
Name VARCHAR2(20); BEGIN
DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE VIEW myview AS
SELECT empno, ename, ''E'' type FROM emp UNION
SELECT dept.deptno, dname, ''D'' FROM dept ');
-- 创建INSTEAD OF 触发器trigger3; DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig3 INSTEAD OF INSERT ON myview REFERENCING NEW n FOR EACH ROW DECLARE
Rows INTEGER; BEGIN
DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…''); IF :n.type = ''D'' THEN SELECT COUNT(*) INTO rows
FROM dept WHERE deptno = :n.empno; IF rows = 0 THEN
DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…''); INSERT INTO dept(deptno, dname, loc) VALUES (:n.empno, :n.ename, ''none’’); ELSE
DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| ''的部门已存在，插入操作失败！''); END IF; ELSE
SELECT COUNT(*) INTO rows
FROM emp WHERE empno = :n.empno; IF rows = 0 THEN
DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’); INSERT INTO emp(empno, ename) VALUES(:n.empno, :n.ename); ELSE
DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| ''的人员已存在，插入操作失败!''); END IF; END IF; END; ');
本文作者：...共分享92篇相关文档
