ORACLE触发器内置程序包教学

1、Oracle 第8章 触发器、内置程序包1、技术目标· 应用触发器· 使用内置程序包2、什么是触发器· 触发器是当特定事件出现时自动执行的存储过程· 特定事件可以是执行更新的DML语句和DDL语句· 触发器不能被显式调用，存储过程可以显示调用触发器的功能有：· 自动生成数据· 自定义复杂的安全权限· 提供审计和日志记录· 启用复杂的业务逻辑触发器可以与特定的表或视图相关联，用于检查对表/视图所做的数据修改，当执行insert、delete、update语句时，可激活触发器代码3、如何创建触发器创建触发器的

2、语法为：CREATE OR REPLACE TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OFinsert | delete | update OF column, column .OR insert | delete | update OF column, column . ONschema.table_or_view_nameREFERENCING NEW AS new_row_name OLD AS old_row_nameFOR EACH ROWWHEN (condition)DECLAREvariable_declationBEGINsta

3、tements;EXCEPTIONexception_handlersEND trigger_name;语法说明：AFTER | BEFORE，指在事件发生之前或之后激活触发器INSTEAD OF，表示可以执行触发器代码来代替导致触发器调用的事件insert | delete | update，指定构成触发器事件的数据操纵类型，update可指定列列表REFERENCING，指定新行(即将更新)和旧行(更新前)的其他名称，默认为NEW和OLDtable_or_view_name，指要创建触发器的表或视图的名称FOR EACH ROW，指定是否对受影响的每行都执行触发器，即行级触发器，如不使用此

4、句，则为语句级触发器WHEN，限制执行触发器的条件，该条件可包括新旧数据值的检查DECLARE.END，一个标准的PL/SQL块使用：在Emp表创建触发器，Sql代码 1. CREATE OR REPLACE TRIGGER biu_emp_deptno 2. -在添加或修改deptNo字段之前触发 3. BEFORE INSERT OR UPDATE OF deptNo 4. ON Emp 5. -行级触发器 6. FOR EACH ROW 7. -列deptNo的新值不等于40 8. WHEN (New.deptNo <> 40) 9. BEGIN 10. -将comm列设置为

5、0 11. :Nm := 0; 12. END; 13. / CREATE OR REPLACE TRIGGER biu_emp_deptno-在添加或修改deptNo字段之前触发BEFORE INSERT OR UPDATE OF deptNoON Emp-行级触发器FOR EACH ROW-列deptNo的新值不等于40WHEN (New.deptNo <> 40)BEGIN -将comm列设置为0 :Nm := 0;END;/注意：使用SHOW ERRORS命令可查看创建触发器时出现的错误4、触发器的组成部分触发器由以下3个部分组成：触发语句，定义激活触发器的 DML 事件和

6、 DDL 事件，如：BEFORE INSERT OR UPDATE OF deptNoON Emp-行级触发器FOR EACH ROW这段代码表示，当对Emp表执行insert语句或对Emp表的deptNo列执行update语句时，触发器会在受影响的每一行上执行一次触发限制，执行触发器的条件，该条件必须为真才能激活触发器，如：-列deptNo的新值不等于40，触发器会执行WHEN (New.deptNo <> 40)触发操作，一些 SQL 语句和代码，在发出了触发器语句且触发限制的值为真时运行，如：BEGIN-将comm列设置为0:Nm := 0;END;5、触发器的类型及使用触发

7、器有如下的类型：每种触发器的作用：使用1：应用行级触发器，Sql代码 1. -创建表TEST_TRG 2. CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20); 3. -创建序列SEQ_TEST 4. CREATE SEQUENCE SEQ_TEST; 5. -为TEST_TRG表创建行级触发器 6. CREATE OR REPLACE TRIGGER BI_TEST_TRG 7. -在insert(添加)或者update(修改)ID字段时触发 8. BEFORE INSERT OR UPDATE OF ID 9. ON TEST_TRG -

8、指定TEST_TRG表 10. FOR EACH ROW -设置为行级触发器 11. -触发器语句部分 12. BEGIN 13. -判断是不是insert语句 14. IF INSERTING THEN 15. -如果是insert操作，将序列的值设置给ID列 16. SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; 17. ELSE 18. -如果不是insert操作，不能修改ID列的值 19. RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！'); 20. END IF; 21. END;

9、22. / -创建表TEST_TRGCREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20);-创建序列SEQ_TESTCREATE SEQUENCE SEQ_TEST;-为TEST_TRG表创建行级触发器CREATE OR REPLACE TRIGGER BI_TEST_TRG-在insert(添加)或者update(修改)ID字段时触发BEFORE INSERT OR UPDATE OF IDON TEST_TRG -指定TEST_TRG表FOR EACH ROW -设置为行级触发器-触发器语句部分BEGIN -判断是不是insert语句 IF

10、 INSERTING THEN -如果是insert操作，将序列的值设置给ID列 SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE -如果不是insert操作，不能修改ID列的值 RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！'); END IF;END;/注意：如果一个触发器由多种语句触发，可用INSERTING、UPDATING、DELETING这些关键字进行检查，对应语句类型使用2：应用语句级触发器，Sql代码 1. CREATE OR REPLACE TRIGGER trgde

11、mo 2. AFTER INSERT OR UPDATE OR DELETE 3. ON order_master 4. BEGIN 5. -根据语句类型输出信息 6. IF UPDATING THEN 7. DBMS_OUTPUT.PUT_LINE('已更新ORDER_MASTER中的数据'); 8. ELSIF DELETING THEN 9. DBMS_OUTPUT.PUT_LINE('已删除ORDER_MASTER中的数据'); 10. ELSIF INSERTING THEN 11. DBMS_OUTPUT.PUT_LINE('已在ORDER_

12、MASTER中插入数据'); 12. END IF; 13. END; 14. / CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT OR UPDATE OR DELETE ON order_masterBEGIN -根据语句类型输出信息 IF UPDATING THEN DBMS_OUTPUT.PUT_LINE('已更新ORDER_MASTER中的数据'); ELSIF DELETING THEN DBMS_OUTPUT.PUT_LINE('已删除ORDER_MASTER中的数据'); ELSIF INSER

13、TING THEN DBMS_OUTPUT.PUT_LINE('已在ORDER_MASTER中插入数据'); END IF;END;/注意：语句级触发器时CREATE TRIGGER命令所创建触发器的默认类型使用3：应用INSTEAD OF触发器，同时向两个表中插入值，Sql代码 1. -创建视图 2. CREATE VIEW ord_view AS 3. SELECT order_master.orderno, order_master.ostatus, 4. order_detail.qty_deld, order_detail.qty_ord 5. FROM order_

14、master, order_detail 6. WHERE order_master.orderno = order_detail.orderno; 7.8. -创建INSTEAD OF触发器 9. CREATE OR REPLACE TRIGGER order_mast_insert 10. INSTEAD OF UPDATE ON ord_view 11. -为NEW关键字取别名n 12. REFERENCING NEW AS n 13. FOR EACH ROW 14. DECLARE 15. -定义游标，访问order_master表 16. CURSOR ecur IS SELECT

15、 * FROM order_master 17. WHERE order_master.orderno = :n.orderno; 18. -定义游标，访问order_detail表 19. CUSEOR dcur IS 20. select * from order_detail 21. WHERE order_detail.orderno = :n.orderno; 22. -定义游标变量 23. a ecur%ROWTYPE; 24. b dcur%ROWTYPE; 25. BEGIN 26. -打开游标 27. OPEN ecur; 28. OPEN dcur; 29. -读取行 30

16、. FETCH ecur into a; 31. FETCH dcur into b; 32. -判断是否有行 33. IF dur%NOTFOUND THEN -没有 34. -添加记录 35. INSERT INTO order_master (orderno, ostatus) 36. VALUES (:n.orderno, :n.ostatus); 37. ELSE -有 38. -修改记录 39. UPDATE order_master SET order_master.ostatus = :n.ostatus 40. WHERE order_master.orderno = :n.

17、orderno; 41. END IF; 42.43. IF ecur%NOTFOUND THEN 44. INSERT INTO order_detail (qty_ord, qty_deld, orderno) 45. VALUES(:n.qty_ord, :n.qty_deld, :n.orderno); 46. ELSE 47. UPDATE order_detail SET 48. order_detail.qty_ord = :n.qty_ord, 49. order_detail.qty_deld = :n.qty_deld 50. WHERE order_detail.orde

18、rno = :n.orderno; 51. END IF; 52. -关闭游标 53. CLOSE ecur; 54. CLOSE dcur; 55. END; 56. / -创建视图CREATE VIEW ord_view ASSELECT order_master.orderno, order_master.ostatus, order_detail.qty_deld, order_detail.qty_ordFROM order_master, order_detailWHERE order_master.orderno = order_detail.orderno;-创建INSTEAD

19、 OF触发器CREATE OR REPLACE TRIGGER order_mast_insert INSTEAD OF UPDATE ON ord_view -为NEW关键字取别名n REFERENCING NEW AS n FOR EACH ROW DECLARE -定义游标，访问order_master表 CURSOR ecur IS SELECT * FROM order_master WHERE order_master.orderno = :n.orderno; -定义游标，访问order_detail表 CUSEOR dcur IS select * from order_det

20、ail WHERE order_detail.orderno = :n.orderno; -定义游标变量 a ecur%ROWTYPE; b dcur%ROWTYPE; BEGIN -打开游标 OPEN ecur; OPEN dcur; -读取行 FETCH ecur into a; FETCH dcur into b; -判断是否有行 IF dur%NOTFOUND THEN -没有 -添加记录 INSERT INTO order_master (orderno, ostatus) VALUES (:n.orderno, :n.ostatus); ELSE -有 -修改记录 UPDATE o

21、rder_master SET order_master.ostatus = :n.ostatus WHERE order_master.orderno = :n.orderno; END IF; IF ecur%NOTFOUND THEN INSERT INTO order_detail (qty_ord, qty_deld, orderno) VALUES(:n.qty_ord, :n.qty_deld, :n.orderno); ELSE UPDATE order_detail SET order_detail.qty_ord = :n.qty_ord, order_detail.qty

22、_deld = :n.qty_deld WHERE order_detail.orderno = :n.orderno; END IF; -关闭游标 CLOSE ecur; CLOSE dcur; END;/注意：使用INSTEAD OF触发器有如下的限制，· 只能在行级使用，不能在语句级使用· 只能应用于视图，不能应用于表使用4：应用模式(DDL)触发器，对用户删除的对象进行日志记录，创建模式触发器的语法为：CREATE OR REPLACE TRIGGER trigger_nameBEFORE | AFTER trigger_eventON schema.SCHEMAW

23、HEN (trigger_condition)trigger_body;Sql代码 1. -创建日志记录表 2. CREATE TABLE dropped_obj 3. ( 4. obj_name VARCHAR2(30), 5. obj_type VARCHAR2(20), 6. drop_date DATE 7. ); 8. -创建触发器 9. CREATE OR REPLACE TRIGGER log_drop_obj 10. -在执行drop语句后触发 11. AFTER DROP ON SCHEMA 12. BEGIN 13. -将被删除对象的信息添加到日志记录表中 14. INSE

24、RT INTO dropped_obj 15. VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE); 16. END; 17. / -创建日志记录表CREATE TABLE dropped_obj( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE);-创建触发器CREATE OR REPLACE TRIGGER log_drop_obj-在执行drop语句后触发AFTER DROP ON SCHEMABEGIN -将被删除对象的信息添加到日志记录表中 INSERT INTO drop