Oracle 第8章触发器、内置程序包1、技术目标∙应用触发器∙使用内置程序包2、什么是触发器∙触发器是当特定事件出现时自动执行的存储过程∙特定事件可以是执行更新的DML语句和DDL语句∙触发器不能被显式调用，存储过程可以显示调用触发器的功能有：∙自动生成数据∙自定义复杂的安全权限∙提供审计和日志记录∙启用复杂的业务逻辑触发器可以与特定的表或视图相关联，用于检查对表/视图所做的数据修改，当执行insert、delete、update语句时，可激活触发器代码3、如何创建触发器创建触发器的语法为：CREATE [OR REPLACE] TRIGGER trigger_name {AFTER | BEFORE | INSTEAD OF}{insert | delete | update [OF column[, column] ...]} [OR {insert | delete | update [OF column[, column] ...] }] ON[schema.]table_or_view_name[REFERENCING [NEW AS new_row_name] [OLD ASold_row_name]][FOR EACH ROW][WHEN (condition)][DECLAREvariable_declation]BEGINstatements;[EXCEPTIONexception_handlers]END [trigger_name];语法说明：AFTER | BEFORE，指在事件发生之前或之后激活触发器INSTEAD OF，表示可以执行触发器代码来代替导致触发器调用的事件insert | delete | update，指定构成触发器事件的数据操纵类型，update可指定列列表REFERENCING，指定新行(即将更新)和旧行(更新前)的其他名称，默认为NEW和OLDtable_or_view_name，指要创建触发器的表或视图的名称FOR EACH ROW，指定是否对受影响的每行都执行触发器，即行级触发器，如不使用此句，则为语句级触发器WHEN，限制执行触发器的条件，该条件可包括新旧数据值的检查DECLARE...END，一个标准的PL/SQL块使用：在Emp表创建触发器，Sql代码1.CREATE OR REPLACE TRIGGER biu_emp_deptno2.--在添加或修改deptNo字段之前触发3.BEFORE INSERT OR UPDATE OF deptNo4.ON Emp5.--行级触发器6.FOR EACH ROW7.--列deptNo的新值不等于408.WHEN (New.deptNo <> 40)9.BEGIN10.--将comm列设置为011.:m := 0;12.END;13./注意：使用SHOW ERRORS命令可查看创建触发器时出现的错误4、触发器的组成部分触发器由以下3个部分组成：触发语句，定义激活触发器的DML 事件和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:m := 0;END;5、触发器的类型及使用触发器有如下的类型：每种触发器的作用：使用1：应用行级触发器，Sql代码1.--创建表TEST_TRG2.CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20));3.--创建序列SEQ_TEST4.CREATE SEQUENCE SEQ_TEST;5.--为TEST_TRG表创建行级触发器6.CREATE OR REPLACE TRIGGER BI_TEST_TRG7.--在insert(添加)或者update(修改)ID字段时触发8.BEFORE INSERT OR UPDATE OF ID9.ON TEST_TRG --指定TEST_TRG表10.FOR EACH ROW --设置为行级触发器11.--触发器语句部分12.BEGIN13.--判断是不是insert语句14.IF INSERTING THEN15.--如果是insert操作，将序列的值设置给ID列16.SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;17.ELSE18.--如果不是insert操作，不能修改ID列的值19.RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');20.END IF;21.END;22./注意：如果一个触发器由多种语句触发，可用INSERTING、UPDATING、DELETING这些关键字进行检查，对应语句类型使用2：应用语句级触发器，Sql代码1.CREATE OR REPLACE TRIGGER trgdemo2.AFTER INSERT OR UPDATE OR DELETE3.ON order_master4.BEGIN5.--根据语句类型输出信息6.IF UPDATING THEN7.DBMS_OUTPUT.PUT_LINE('已更新ORDER_MASTER中的数据');8.ELSIF DELETING THEN9.DBMS_OUTPUT.PUT_LINE('已删除ORDER_MASTER中的数据');10.ELSIF INSERTING THEN11.DBMS_OUTPUT.PUT_LINE('已在ORDER_MASTER中插入数据');12.END IF;13.END;14./注意：语句级触发器时CREATE TRIGGER命令所创建触发器的默认类型使用3：应用INSTEAD OF触发器，同时向两个表中插入值，Sql代码1.--创建视图2.CREATE VIEW ord_view AS3.SELECT order_master.orderno, order_master.ostatus,4.order_detail.qty_deld, order_detail.qty_ord5.FROM order_master, order_detail6.WHERE order_master.orderno = order_detail.orderno;7.8.--创建INSTEAD OF触发器9.CREATE OR REPLACE TRIGGER order_mast_insert10.INSTEAD OF UPDATE ON ord_view11.--为NEW关键字取别名n12.REFERENCING NEW AS n13.FOR EACH ROW14.DECLARE15.--定义游标，访问order_master表16.CURSOR ecur IS SELECT * FROM order_master17.WHERE order_master.orderno = :n.orderno;18.--定义游标，访问order_detail表19.CUSEOR dcur IS20.select * from order_detail21.WHERE order_detail.orderno = :n.orderno;22.--定义游标变量23.a ecur%ROWTYPE;24.b dcur%ROWTYPE;25.BEGIN26.--打开游标27.OPEN ecur;28.OPEN dcur;29.--读取行30.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.ostatus40.WHERE order_master.orderno = :n.orderno;41.END IF;42.43.IF ecur%NOTFOUND THEN44.INSERT INTO order_detail (qty_ord, qty_deld, orderno)45.VALUES(:n.qty_ord, :n.qty_deld, :n.orderno);46.ELSE47.UPDATE order_detail SET48.order_detail.qty_ord = :n.qty_ord,49.order_detail.qty_deld = :n.qty_deld50.WHERE order_detail.orderno = :n.orderno;51.END IF;52.--关闭游标53.CLOSE ecur;54.CLOSE dcur;55.END;56./注意：使用INSTEAD OF触发器有如下的限制，∙只能在行级使用，不能在语句级使用∙只能应用于视图，不能应用于表使用4：应用模式(DDL)触发器，对用户删除的对象进行日志记录，创建模式触发器的语法为：CREATE OR REPLACE TRIGGER trigger_name {BEFORE | AFTER} trigger_eventON [schema.]SCHEMAWHEN (trigger_condition)trigger_body;Sql代码1.--创建日志记录表2.CREATE TABLE dropped_obj3.(4.obj_name VARCHAR2(30),5.obj_type VARCHAR2(20),6.drop_date DATE7.);8.--创建触发器9.CREATE OR REPLACE TRIGGER log_drop_obj10.--在执行drop语句后触发11.AFTER DROP ON SCHEMA12.BEGIN13.--将被删除对象的信息添加到日志记录表中14.INSERT INTO dropped_obj15.VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);16.END;17./使用5：应用数据库级触发器，在数据库启动后执行，CREATE OR REPLACE TRIGGER system_startup--系统启动时触发AFTER STARTUP ON DATEBASEBEGIN--加入所需代码END;/6、启动、禁用、删除触发器，查看触发器信息启用和禁用触发器：ALTER TRIGGER 触发器名DISABLE; --禁用ALTER TRIGGER 触发器名ENABLE; --启用删除触发器：DROP TRIGGER 触发器名;查看触发器信息，使用USER_TRIGGERS数据字典：使用1：查看为表EMP设置的触发器名select TRIGGER_NAME from USER_TRIGGERSWHERE TABLE_NAME = 'EMP';使用2：查看触发器BIU_EMP_DEPTNO的类型、触发事件、触发条件，select TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE from USER_TRIGGERSWHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';7、内置程序包Oracle提供了许多内置程序包，用于扩展数据库功能，数据库用户SYS 拥有所有程序包，程序包被定义为公有同义词，并将执行权限授予了PUBLIC用户组，任何用户都可访问，部分内置程序包如下：8、总结∙触发器是当特定事件出现时自动执行的存储过程∙触发器分为DML 触发器、DDL 触发器和数据库级触发器三种类型∙DML触发器的三种类型为行级触发器、语句级触发器和INSTEAD OF触发器∙了解一些常用的内置程序包。