oracle觸發(fā)器trigger簡單教程

1、發(fā)器，使其執(zhí)行觸發(fā)操作。 l 觸發(fā)頻率：說明觸發(fā)器內(nèi)定義的動作被執(zhí)行的次數(shù)。即語句級(STATEMENT觸發(fā)器和行級(ROW觸發(fā)器。 語句級(STATEMENT觸發(fā)器：是指當某觸發(fā)事件發(fā)生時，該觸發(fā)器只執(zhí)行一次； 行級(ROW觸發(fā)器：是指當某觸發(fā)事件發(fā)生時，對受到該操作影響的每一行數(shù)據(jù)，觸發(fā)器都單獨執(zhí)行一次。 編寫觸發(fā)器時，需要注意以下幾點： l 觸發(fā)器不接受參數(shù)。 l 一個表上最多可有12個觸發(fā)器，但同一時間、同一事件、同一類型的觸發(fā)器只能有一個。并各觸發(fā)器之間不能有矛盾。 l 在一個表上的觸發(fā)器越多，對在該表上的DML操作的性能影響就越大。 l 觸發(fā)器最大為32KB。若確實需要，可以先建立

2、過程，然后在觸發(fā)器中用CALL語句進行調(diào)用。 l 在觸發(fā)器的執(zhí)行部分只能用DML語句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語句（CREATE、ALTER、DROP）。 l 觸發(fā)器中不能包含事務控制語句(COMMIT，ROLLBACK，SAVEPOINT。因為觸發(fā)器是觸發(fā)語句的一部分，觸發(fā)語句被提交、回退時，觸發(fā)器也被提交、回退了。 l 在觸發(fā)器主體中調(diào)用的任何過程、函數(shù)，都不能使用事務控制語句。 l 在觸發(fā)器主體中不能申明任何Long和blob變量。新值new和舊值old也不能向表中的任何long和blob列。 l 不同類型的觸發(fā)器(如DML觸發(fā)器、INST

3、EAD OF觸發(fā)器、系統(tǒng)觸發(fā)器的語法格式和作用有較大區(qū)別。 8.2 創(chuàng)建觸發(fā)器 創(chuàng)建觸發(fā)器的一般語法是: CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER INSERT | DELETE | UPDATE OF column , column OR INSERT | DELETE | UPDATE OF column , column . ON schema.table_name | schema.view_name REFERENCING OLD AS old | NEW AS new| PARENT as parent FOR EA

4、CH ROW WHEN condition PL/SQL_BLOCK | CALL procedure_name; 其中： BEFORE 和AFTER指出觸發(fā)器的觸發(fā)時序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在執(zhí)行觸發(fā)事件之前觸發(fā)當前所創(chuàng)建的觸發(fā)器，后觸發(fā)是在執(zhí)行觸發(fā)事件之后觸發(fā)當前所創(chuàng)建的觸發(fā)器。 FOR EACH ROW選項說明觸發(fā)器為行觸發(fā)器。行觸發(fā)器和語句觸發(fā)器的區(qū)別表現(xiàn)在：行觸發(fā)器要求當一個DML語句操走影響數(shù)據(jù)庫中的多行數(shù)據(jù)時，對于其中的每個數(shù)據(jù)行，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器；而語句觸發(fā)器將整個語句操作作為觸發(fā)事件，當它符合約束條件時，激活一次觸發(fā)器。當省略FOR EA

5、CH ROW 選項時，BEFORE 和AFTER 觸發(fā)器為語句觸發(fā)器，而INSTEAD OF 觸發(fā)器則只能為行觸發(fā)器。 REFERENCING 子句說明相關名稱，在行觸發(fā)器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照當前的新、舊列值，默認的相關名稱 E。 2）。UPDATING （column_1,column_2,column_x）：當觸發(fā)事件是UPDATE 時，如果修改了column_x列，則取值為TRUE，否則為FALSE。其中column_x是可選的。 3）。DELETING：當觸發(fā)事件是DELETE時，則取值為TRUE，否則為FALSE。 解發(fā)對象：指定觸發(fā)器是創(chuàng)建在哪個表

6、、視圖上。 l 觸發(fā)類型：是語句級還是行級觸發(fā)器。 l 觸發(fā)條件：由WHEN子句指定一個邏輯表達式，只允許在行級觸發(fā)器上指定觸發(fā)條件，指定UPDATING后面的列的列表。 問題：當觸發(fā)器被觸發(fā)時，要使用被插入、更新或刪除的記錄中的列值，有時要使用操作前、 后列的值. 實現(xiàn): :NEW 修飾符訪問操作完成后列的值 :OLD 修飾符訪問操作完成前列的值 特性 INSERT UPDATE DELETE OLD NULL 實際值 實際值 NEW 實際值 實際值 NULL 例1: 建立一個觸發(fā)器, 當職工表 emp 表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日志表中去。 CREATE TABLE e

7、mp_his AS SELECT * FROM EMP WHERE 1=2; 創(chuàng)建emp表的相同結構，0數(shù)據(jù)的表。 CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE -指定觸發(fā)時機為刪除操作前觸發(fā) ON scott.emp FOR EACH ROW -說明創(chuàng)建的是行級觸發(fā)器 BEGIN -將修改前數(shù)據(jù)插入到日志記錄表 del_emp ,以供監(jiān)督使用。 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate VALUES( :old.deptno,

8、 :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :m, :old.hiredate ; END; DELETE emp WHERE empno=7788; DROP TABLE emp_his; DROP TRIGGER del_emp; 查看觸發(fā)器。 SQL> select trigger_name,trigger_type,table_name,status,TRIGGER_BODY from user_triggers; 例2：限制對Departments表修改（包括INSERT,DELETE,UPDATE）的時間范圍，

9、即不允許在非工作時間修改departments表。 CREATE OR REPLACE TRIGGER tr_dept_time BEFORE INSERT OR DELETE OR UPDATE ON departments BEGIN IF (TO_CHAR(sysdate,'DAY' IN ('星期六', '星期日' OR (TO_CHAR(sysdate, 'HH24:MI' NOT BETWEEN '08:30' AND '18:00' THEN RAISE_APPLICATION_ERR

10、OR(-20001, '不是上班時間，不能修改departments表' END IF; END; 例3：限定只對部門號為80的記錄進行行觸發(fā)器操作。 CREATE OR REPLACE TRIGGER tr_emp_sal_comm BEFORE UPDATE OF salary, commission_pct OR DELETE ON HR.employees FOR EACH ROW WHEN (old.department_id = 80 BEGIN CASE WHEN UPDATING ('salary' THEN IF :NEW.salary <

11、; :old.salary THEN RAISE_APPLICATION_ERROR(-20001, '部門80的人員的工資不能降' END IF; WHEN UPDATING ('commission_pct' THEN IF :NEW.commission_pct < :mission_pct THEN RAISE_APPLICATION_ERROR(-20002, '部門80的人員的獎金不能降' END IF; WHEN DELETING THEN RAISE_APPLICATION_ERROR(-20003, '不能刪除 S

12、QL 程序條使其所使用的調(diào)試代碼。 8.3 刪除和使能觸發(fā)器 l 刪除觸發(fā)器： DROP TRIGGER trigger_name; 當刪除其他用戶模式中的觸發(fā)器名稱，需要具有DROP ANY TRIGGER系統(tǒng)權限，當刪除建立在數(shù)據(jù)庫上的觸發(fā)器時，用戶需要具有ADMINISTER DATABASE TRIGGER系統(tǒng)權限。 此外，當刪除表或視圖時，建立在這些對象上的觸發(fā)器也隨之刪除。 l 禁用或啟用觸發(fā)器 數(shù)據(jù)庫TRIGGER 的狀態(tài)： 有效狀態(tài)(ENABLE：當觸發(fā)事件發(fā)生時，處于有效狀態(tài)的數(shù)據(jù)庫觸發(fā)器TRIGGER 將被觸發(fā)。 無效狀態(tài)(DISABLE：當觸發(fā)事件發(fā)生時，處于無效狀態(tài)的數(shù)

13、據(jù)庫觸發(fā)器TRIGGER 將不會被觸發(fā)，此時就跟沒有這個數(shù)據(jù)庫觸發(fā)器(TRIGGER 一樣。 數(shù)據(jù)庫TRIGGER的這兩種狀態(tài)可以互相轉換。格式為： ALTER TIGGER trigger_name DISABLE | ENABLE ; -例：ALTER TRIGGER emp_view_delete DISABLE; ALTER TRIGGER語句一次只能改變一個觸發(fā)器的狀態(tài)，而ALTER TABLE語句則一次能夠改變與指定表相關的所有觸發(fā)器的使用狀態(tài)。格式為： ALTER TABLE schema.table_name ENABLE|DISABLE ALL TRIGGERS; -例：使表

14、EMP 上的所有TRIGGER 失效： ALTER TABLE emp DISABLE ALL TRIGGERS; 8.4 觸發(fā)器和數(shù)據(jù)字典 相關數(shù)據(jù)字典：USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES, STATUS, ACTION_TYPE FROM user_triggers; 8.5 數(shù)據(jù)庫觸發(fā)器的應用舉例 例1：創(chuàng)建一個DML語句級觸發(fā)器，當對emp表

15、執(zhí)行INSERT, UPDATE, DELETE 操作時，它自動更新dept_summary 表中的數(shù)據(jù)。由于在PL/SQL塊中不能直接調(diào)用DDL語句，所以，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過程，由它執(zhí)行DDL語句創(chuàng)建觸發(fā)器。 CREATE TABLE dept_summary( Deptno NUMBER(2, Sal_sum NUMBER(9, 2, Emp_count NUMBER; INSERT INTO dept_summary(deptno, sal_sum, emp_count SELECT deptno, SUM(sal,

16、COUNT(* FROM emp GROUP BY deptno; -創(chuàng)建一個PL/SQL過程disp_dept_summary -在觸發(fā)器中調(diào)用該過程顯示dept_summary標中的數(shù)據(jù)。 CREATE OR REPLACE PROCEDURE disp_dept_summary IS Rec dept_summary%ROWTYPE; CURSOR c1 IS SELECT * FROM dept_summary; BEGIN OPEN c1; FETCH c1 INTO REC; DBMS_OUTPUT.PUT_LINE('deptno sal_sum emp_count

17、9; DBMS_OUTPUT.PUT_LINE('-' WHILE c1%FOUND LOOP DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6| To_char(rec.sal_sum, '$999,999.99'| LPAD(rec.emp_count, 13; FETCH c1 INTO rec; END LOOP; CLOSE c1; END; BEGIN DBMS_OUTPUT.PUT_LINE('插入前' Disp_dept_summary(; DBMS_UTILITY.EXEC_DDL_STATEMEN

18、T(' CREATE OR REPLACE TRIGGER trig1 AFTER INSERT OR DELETE OR UPDATE OF sal ON emp BEGIN DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig1 觸發(fā)器'' DELETE FROM dept_summa ry; INSERT INTO dept_summary(deptno, sal_sum, emp_count SELECT deptno, SUM(sal, COUNT(* FROM emp GROUP BY deptno; END; ' INSE

19、RT INTO dept(deptno, dname, loc VALUES(90, demo_dept, none_loc; INSERT INTO emp(ename, deptno, empno, sal VALUES(USER, 90, 9999, 3000; DBMS_OUTPUT.PUT_LINE('插入后' Disp_dept_summary(; UPDATE emp SET sal=1000 WHERE empno=9999; DBMS_OUTPUT.PUT_LINE('修改后' Disp_dept_summary(; DELETE FROM e

20、mp WHERE empno=9999; DELETE FROM dept WHERE deptno=90; DBMS_OUTPUT.PUT_LINE('刪除后' Disp_dept_summary(; DBMS_UTILITY.EXEC_DDL_STATEMENT(DROP TRIGGER trig1; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|'-'|SQLERRM; END; 例2：創(chuàng)建DML語句行級觸發(fā)器。當對emp表執(zhí)行INSERT, UPDATE, DELETE 操作時，它自動更新

21、dept_summary 表中的數(shù)據(jù)。由于在PL/SQL塊中不能直接調(diào)用DDL語句，所以，利用ORACLE內(nèi)置包DBMS_UTILITY中的EXEC_DDL_STATEMENT過程，由它執(zhí)行DDL語句創(chuàng)建觸發(fā)器。 BEGIN DBMS_OUTPUT.PUT_LINE('插入前' Disp_dept_summary(; DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_update AFTER UPDATE OF sal ON emp REFERENCING OLD AS old_emp N

22、EW AS new_emp FOR EACH ROW WHEN (old_emp.sal != new_emp.sal BEGIN DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_update 觸發(fā)器'' DBMS_OUTPUT.PUT_LINE(''sal 舊值：''| :old_emp.sal; DBMS_OUTPUT.PUT_LINE(''sal 新值：''| :new_emp.sal; UPDATE dept_summary SET sal_sum=sal_sum + :n

23、ew_emp.sal - :old_emp.sal WHERE deptno = :new_emp.deptno; END;' ; DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_insert AFTER INSERT ON emp REFERENCING NEW AS new_emp FOR EACH ROW DECLARE I NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_insert 觸發(fā)器'' SELECT

24、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; END;' ; DBMS_UTILITY.EXEC_

25、DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_delete AFTER DELETE ON emp REFERENCING OLD AS old_emp FOR EACH ROW DECLARE I NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_delete 觸發(fā)器'' SELECT emp_count INTO I FROM dept_summary WHERE deptno = :old_emp.deptno; IF I >1 THEN UPDAT

26、E 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, d

27、eptno, 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 dept

28、no=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'

29、EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|'-'|SQLERRM; END; 例3：利用ORACLE提供的條件謂詞INSERTING、UPDATING和DELETING創(chuàng)建與例2具有相同功能的觸發(fā)器。 BEGIN DBMS_OUTPUT.PUT_LINE('插入前' Disp_dept_summary(; DBMS_UTILITY.EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2 AFTER INSERT OR DELETE OR

30、 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(''正在執(zhí)行trig2 觸發(fā)器'' DBMS_OUTPUT.PUT_LINE(''sal 舊值：''| :old_emp.sal; DBMS_OUTPUT.PUT_LINE(''

31、;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(''正在執(zhí)行trig2觸發(fā)器'' SELECT COUNT(* INTO I FROM dept_summary WHERE deptno = :new_emp.deptno; IF I > 0 THEN

32、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(''正在執(zhí)行trig2觸發(fā)器'' SELECT emp_count INTO I FROM dept_summary WHERE deptn

33、o = :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_dep

34、t', '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('修改后&

35、#39; 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：創(chuàng)建INSTE

36、AD OF 觸發(fā)器。首先創(chuàng)建一個視圖myview, 由于該視圖是復合查詢所產(chǎn)生的視圖，所以不能執(zhí)行DML語句。根據(jù)用戶對視圖所插入的數(shù)據(jù)判斷需要將數(shù)據(jù)插入到哪個視圖基表中，然后對該基表執(zhí)行插入操作。 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, dna

37、me, ''D'' FROM dept ' - 創(chuàng)建INSTEAD OF 觸發(fā)器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(''正在執(zhí)行trig3觸發(fā)器'' IF :n.type = '&

38、#39;D'' THEN SELECT COUNT(* INTO rows FROM dept WHERE deptno = :n.empno; IF rows = 0 THEN DBMS_OUTPUT.PUT_LINE(''向dept表中插入數(shù)據(jù)'' INSERT INTO dept(deptno, dname, loc VALUES (:n.empno, :n.ename, ''none; ELSE DBMS_OUTPUT.PUT_LINE(''編號為''| :n.empno| '

39、9;的部門已存在，插入操作失敗！'' END IF; ELSE SELECT COUNT(* INTO rows FROM emp WHERE empno = :n.empno; IF rows = 0 THEN DBMS_OUTPUT.PUT_LINE('向emp表中插入數(shù)據(jù); INSERT INTO emp(empno, ename VALUES(:n.empno, :n.ename; ELSE DBMS_OUTPUT.PUT_LINE(''編號為''| :n.empno| ''的人員已存在，插入操作失敗!'&

40、#39; 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,

41、 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事件屬性函數(shù)，創(chuàng)建一個系統(tǒng)事件觸發(fā)器。首先創(chuàng)建一個事件日志表eventlog，由它存 儲用戶在當前數(shù)據(jù)庫中所

42、創(chuàng)建的數(shù)據(jù)庫對象，以及用戶的登陸和注銷、數(shù)據(jù)庫的啟動和關閉等事件，之后創(chuàng)建trig4_ddl、trig4_before和trig4_after觸發(fā)器，它們調(diào)用事件屬性函數(shù)將各個事件記錄到eventlog數(shù)據(jù)表中。 BEGIN - 創(chuàng)建用于記錄事件日志的數(shù)據(jù)表 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 NUL

43、L, Srv_error NUMBER NULL, Username VARCHAR2(30 NULL, Obj_type VARCHAR2(20 NULL, Obj_name VARCHAR2(30 NULL, Obj_owner VARCHAR2(30 NULL ' - 創(chuàng)建DDL觸發(fā)器trig4_ddl DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig4_ddl AFTER CREATE OR ALTER OR DROP ON DATABASE DECLARE Event VARCHAR2(2

44、0; 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; EN

45、D; ' - 創(chuàng)建LOGON、STARTUP和SERVERERROR 事件觸發(fā)器 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 eve

46、nt = ''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 :=

47、 DATABASE_NAME; INSERT INTO eventlog(eventname, inst_num, db_name VALUES(event, instance, dbname; END IF; END; ' - 創(chuàng)建LOGOFF和SHUTDOWN 事件觸發(fā)器 DBMS_UTILITY.EXEC_DDL_STATEMENT(' CREATE OR REPLACE TRIGGER trig4_before BEFORE LOGOFF OR SHUTDOWN ON DATABASE DECLARE Event VARCHAR2(20; Instance NUMBER

48、; Dbname VARCHAR2(50; User VARCHAR2(30; BEGIN Event := SYSEVENT; IF event = ''LOGOFF'' THEN User := LOGIN_USER; INSERT INTO eventlog(eventname, username VALUES(eve nt, user; ELSE Instance := INSTANCE_NUM; Dbname := DATABASE_NAME; INSERT INTO eventlog(eventname, inst_num, db_name VALU

49、ES(event, instance, 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_type, obj_nam

50、e, 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 數(shù)據(jù)庫觸發(fā)器的應用實例 用戶可以使用數(shù)據(jù)庫觸發(fā)器實現(xiàn)各種功能： l 復雜的審計功能； 例：將EMP 表的變化情況記錄到AUDIT_TABLE和AUDIT_TABLE_VALUES中。 CREATE TABLE audit_table( Audit_id NUM

51、BER, 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,2, New_val NUMBER(7,2; CREATE SEQUENCE audit_seq START WITH 1000 INCREMENT

52、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, t

53、ime_now, terminal, 'EMP', 'INSERT', :new.empno; ELSIF DELETING THEN INSERT INTO audit_table VALUES(audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'DELETE', :old.empno; ELSE INSERT INTO audit_table VALUES(audit_seq.NEXTVAL, user, time_now, terminal, 'EMP',

54、 'UPDATE', :old.empno; IF UPDATING('SAL' THEN INSERT INTO audit_table_val VALUES(audit_seq.CURRVAL, 'SAL', :old.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;

55、 l 增強數(shù)據(jù)的完整性管理； 例：修改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.updates