Oracle触发器

例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,‘‘E‘‘ type FROM emp

??????????? UNION

??????????? SELECT dept.deptno,‘‘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,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 VARCHAR2(20) NULL,

??????????? Obj_name VARCHAR2(30) NULL,

??????????? Obj_owner VARCHAR2(30) NULL

??????? )

??? ‘);

??? -- 创建DDL触发器trig4_ddl

??? DBMS_UTILITY.EXEC_DDL_STATEMENT(‘

??????? CREATE OR REPLACE TRIGGER trig4_ddl

??????????? AFTER CREATE OR ALTER OR DROP

ON DATABASE

??????? DECLARE

??????????? Event VARCHAR2(20);

??????????? Typ VARCHAR2(20);

??????????? Name VARCHAR2(30);

??????????? Owner VARCHAR2(30);

??????? BEGIN

??????????? -- 读取DDL事件属性

??????????? Event := SYSEVENT;

??????????? Typ := DICTIONARY_OBJ_TYPE;

??????????? Name := DICTIONARY_OBJ_NAME;

??????????? Owner := DICTIONARY_OBJ_OWNER;

??????????? --将事件属性插入到事件日志表中

??????????? INSERT INTO scott.eventlog(eventname,obj_type,obj_name,obj_owner)

??????????????? VALUES(event,typ,name,owner);

??????? END;

??? ‘);

??? -- 创建LOGON、STARTUP和SERVERERROR 事件触发器

??? DBMS_UTILITY.EXEC_DDL_STATEMENT(‘

??????? CREATE OR REPLACE TRIGGER trig4_after

??????????? AFTER LOGON OR STARTUP OR SERVERERROR

????? ON DATABASE

??????? DECLARE

??????????? Event VARCHAR2(20);

??????????? Instance NUMBER;

??????????? Err_num NUMBER;

??????????? Dbname VARCHAR2(50);

??????????? User VARCHAR2(30);

??????? BEGIN

??????????? Event := SYSEVENT;

??????????? IF event = ‘‘LOGON‘‘ THEN

??????????????? User := LOGIN_USER;

??????????????? INSERT INTO eventlog(eventname,username)

??????????????????? VALUES(event,user);

??????????? ELSIF event = ‘‘SERVERERROR‘‘ THEN

??????????????? Err_num := SERVER_ERROR(1);

??????????????? INSERT INTO eventlog(eventname,srv_error)

??????????????????? VALUES(event,err_num);

??????????? ELSE

??????????????? Instance := INSTANCE_NUM;

??????????????? Dbname := DATABASE_NAME;

??????????????? INSERT INTO eventlog(eventname,inst_num,db_name)

??????????????????? VALUES(event,instance,dbname);

????? END IF;

??? END;

? ‘);

? -- 创建LOGOFF和SHUTDOWN 事件触发器

? DBMS_UTILITY.EXEC_DDL_STATEMENT(‘

??? CREATE OR REPLACE TRIGGER trig4_before

????? BEFORE LOGOFF OR SHUTDOWN

????? ON DATABASE

??? DECLARE

????? Event VARCHAR2(20);

????? Instance NUMBER;

????? Dbname VARCHAR2(50);

????? User VARCHAR2(30);

??? BEGIN

????? Event := SYSEVENT;

????? IF event = ‘‘LOGOFF‘‘ THEN

??????? User := LOGIN_USER;

??????? INSERT INTO eventlog(eventname,username)

????????? VALUES(event,user);

????? ELSE

??????? Instance := INSTANCE_NUM;

??????? Dbname := DATABASE_NAME;

??????? INSERT INTO eventlog(eventname,db_name)

????????? VALUES(event,dbname);

????? END IF;

??? END;

? ‘);

END;

CREATE TABLE mydata(mydate NUMBER);

CONNECT SCOTT/TIGER

COL eventname FORMAT A10

COL eventdate FORMAT A12

COL username FORMAT A10

COL obj_type FORMAT A15

COL obj_name FORMAT A15

COL obj_owner FORMAT A10

SELECT eventname,eventdate,obj_owner,username,Srv_error

? FROM eventlog;

DROP TRIGGER trig4_ddl;

DROP TRIGGER trig4_before;

DROP TRIGGER trig4_after;

DROP TABLE eventlog;

DROP TABLE mydata;

8.6?? 数据库触发器的应用实例

用户可以使用数据库触发器实现各种功能：

l???????? 复杂的审计功能；

例：将EMP 表的变化情况记录到AUDIT_TABLE和AUDIT_TABLE_VALUES中。

CREATE TABLE audit_table(

??? Audit_id???? NUMBER,

??? User_name VARCHAR2(20),

??? Now_time DATE,

??? Terminal_name VARCHAR2(10),

??? Table_name VARCHAR2(10),

??? Action_name VARCHAR2(10),

??? Emp_id NUMBER(4));

CREATE TABLE audit_table_val(

??? Audit_id NUMBER,

??? Column_name VARCHAR2(10),

??? Old_val NUMBER(7,

??? New_val NUMBER(7,2));

CREATE SEQUENCE audit_seq

??? START WITH 1000

??? INCREMENT BY 1

??? NOMAXVALUE

??? NOCYCLE NOCACHE;

CREATE OR REPLACE TRIGGER audit_emp

??? AFTER INSERT OR UPDATE OR DELETE ON emp

??? FOR EACH ROW

DECLARE

??? Time_now DATE;

??? Terminal CHAR(10);

BEGIN

??? Time_now:=sysdate;

??? Terminal:=USERENV(‘TERMINAL‘);

??? IF INSERTING THEN

??????? INSERT INTO audit_table

??? VALUES(audit_seq.NEXTVAL,user,time_now,

?????????? terminal,‘EMP‘,‘INSERT‘,:new.empno);

??? ELSIF DELETING THEN

??????? INSERT INTO audit_table

??? VALUES(audit_seq.NEXTVAL,‘DELETE‘,:old.empno);

??? ELSE

??????? INSERT INTO audit_table

??? VALUES(audit_seq.NEXTVAL,‘UPDATE‘,:old.empno);

??????? IF UPDATING(‘SAL‘) THEN

??????????? INSERT INTO audit_table_val

??????????????? VALUES(audit_seq.CURRVAL,‘SAL‘,:new.sal);

??????? ELSE UPDATING(‘DEPTNO‘)

??????????? INSERT INTO audit_table_val

??????????????? VALUES(audit_seq.CURRVAL,‘DEPTNO‘,:old.deptno,:new.deptno);

??????? END IF;

??? END IF;

END;

l???????? 增强数据的完整性管理；

例：修改DEPT表的DEPTNO列时，同时把EMP表中相应的DEPTNO也作相应的修改；

CREATE SEQUENCE update_sequence

??? INCREMENT BY 1

??? START WITH 1000

??? MAXVALUE 5000 CYCLE;

ALTER TABLE emp

??? ADD update_id NUMBER;

CREATE OR REPLACE PACKAGE integritypackage AS

??? Updateseq NUMBER;

END integritypackage;

CREATE OR REPLACE PACKAGE BODY integritypackage AS

END integritypackage;

CREATE OR REPLACE TRIGGER dept_cascade1

??? BEFORE UPDATE OF deptno ON dept

DECLARE

??? Dummy NUMBER;

BEGIN

??? SELECT update_sequence.NEXTVAL INTO dummy FROM dual;

??? Integritypackage.updateseq:=dummy;

END;

CREATE OR REPLACE TRIGGER dept_cascade2

??? AFTER DELETE OR UPDATE OF deptno ON dept

??? FOR EACH ROW

BEGIN

??? IF UPDATING THEN

??????? UPDATE emp SET deptno=:new.deptno,

???? update_id=integritypackage.updateseq

??????? WHERE emp.deptno=:old.deptno AND update_id IS NULL;

??? END IF;

??? IF DELETING THEN

??????? DELETE FROM emp

??????????? WHERE emp.deptno=:old.deptno;

??? END IF;

END;

CREATE OR REPLACE TRIGGER dept_cascade3

??? AFTER UPDATE OF deptno ON dept

BEGIN

??? UPDATE emp SET update_id=NULL

??????? WHERE update_id=integritypackage.updateseq;

END;

SELECT * FROM EMP ORDER BY DEPTNO;

UPDATE dept SET deptno=25 WHERE deptno=20;

l???????? 帮助实现安全控制；

例：保证对EMP表的修改仅在工作日的工作时间；

CREATE TABLE company_holidays(day DATE);

INSERT INTO company_holidays

??? VALUES(sysdate);

INSERT INTO company_holidays

VALUES(TO_DATE(‘21-10月-01‘,‘DD-MON-YY‘));

CREATE OR REPLACE TRIGGER emp_permit_change

??? BEFORE INSERT OR DELETE OR UPDATE ON emp

DECLARE

??? Dummy NUMBER;

??? Not_on_weekends EXCEPTION;

??? Not_on_holidays EXCEPTION;

??? Not_working_hours EXCEPTION;

BEGIN

??? /* check for weekends */

IF TO_CHAR(SYSDATE,‘星期日‘) THEN

??? RAISE not_on_weekends;

END IF;

??? /* check for company holidays */

SELECT COUNT(*) INTO dummy FROM company_holidays

??? WHERE TRUNC(day)=TRUNC(SYSDATE);

IF dummy >0 THEN

??? RAISE not_on_holidays;

END IF;

??? /* check for work hours(8:00 AM to 18:00 PM */

IF (TO_CHAR(SYSDATE,‘HH24‘)<8 OR TO_CHAR(SYSDATE,‘HH24‘)>18) THEN

? RAISE not_working_hours;

END IF;

EXCEPTION

? WHEN not_on_weekends THEN

??? RAISE_APPLICATION_ERROR(-20324,

‘May not change employee table during the weekends‘);

? WHEN not_on_holidays THEN

??? RAISE_APPLICATION_ERROR(-20325,

‘May not change employee table during a holiday‘);

? WHEN not_working_hours THEN

??? RAISE_APPLICATION_ERROR(-20326,

‘May not change employee table during no_working hours‘);

（编辑：安卓应用网_ASP源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!