w_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;
');
INSERT INTO myview VALUES (70, 'demo', 'D');
INSERT INTO myview VALUES (9999, USER, 'E');
SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70;
DBMS_OUTPUT.PUT_LINE('员工编号：'||TO_CHAR(no)||'姓名：'||name);
SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;
DBMS_OUTPUT.PUT_LINE('部门编号：'||TO_CHAR(no)||'姓名：'||name);
DELETE FROM emp WHERE empno=9999;
DELETE FROM dept WHERE deptno=70;
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3');
END;
?
?
例5：利用ORACLE事件属性函数，创建一个系统事件触发器。首先创建一个事件日志表eventlog，由它存储用户在当前数据库中所创建的数据库对象，以及用户的登陆和注销、数据库的启动和关闭等事件，之后创建trig4_ddl、trig4_before和trig4_after触发器，它们调用事件属性函数将各个事件记录到eventlog数据表中。
BEGIN
-- 创建用于记录事件日志的数据表
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE TABLE eventlog(
Eventname VARCHAR2(20) NOT NULL,
Eventdate date default sysdate,
Inst_num NUMBER NULL,
Db_name VARCHAR2(50) NULL,
Srv_error NUMBER NULL,
Username VARCHAR2(30) NULL,
Obj_type VARCH