数据库触发器和内置程序包

1、讲师：林强讲师：林强 本章将讨论：本章将讨论： 数据库触发器数据库触发器 内置程序包内置程序包 触发器类似于函数和过程，它们都是具有声明部分、执行触发器类似于函数和过程，它们都是具有声明部分、执行部分和异常处理部分的命名部分和异常处理部分的命名PL/SQLPL/SQL块。像包一样，触发器块。像包一样，触发器必须在数据库中以独立对象的身份存储，并且不能与包和必须在数据库中以独立对象的身份存储，并且不能与包和块具有本地关系。我们在前两章中已经讲过，过程是显式块具有本地关系。我们在前两章中已经讲过，过程是显式地通过过程调用从其他块中执行的，同时，过程调用可以地通过过程调用从其他块中执行的，同时，过程

2、调用可以传递参数。与之相反传递参数。与之相反, ,触发器是在事件发生时隐式地运行触发器是在事件发生时隐式地运行的，并且触发器不能接收参数。运行触发器的方式叫做激的，并且触发器不能接收参数。运行触发器的方式叫做激发（发（firingfiring）触发器，触发事件可以是对数据库表的）触发器，触发事件可以是对数据库表的DMLDML（INSERTINSERT、UPDATEUPDATE或或DELETEDELETE）操作或某种视图的操作）操作或某种视图的操作( Vi e ( Vi e w )w )。Oracle8iOracle8i把触发器功能扩展到了可以激发系统事件，把触发器功能扩展到了可以激发系统事件，

3、如数据库的启动和关闭，以及某种如数据库的启动和关闭，以及某种DDLDDL操作。操作。 维护在表创建阶段通过声明限制无法实现的复杂维护在表创建阶段通过声明限制无法实现的复杂完整性限制。完整性限制。 通过记录修改内容和修改者来审计表中的信息。通过记录修改内容和修改者来审计表中的信息。 在表内容发生变更时，自动通知其他程序采取相在表内容发生变更时，自动通知其他程序采取相应的处理。应的处理。 在订阅发布环境下，发布有关各种事件的信息。在订阅发布环境下，发布有关各种事件的信息。 数据库触发器数据库触发器 是是 PL/SQL 块或存储过程块或存储过程 是在对关联表执行是在对关联表执行 DML 操作时触发的

4、操作时触发的 是隐式执行的是隐式执行的 还可能具有声明部分和异常处理部分还可能具有声明部分和异常处理部分create table STUDENTS(create table STUDENTS(ID INTEGER not null,ID INTEGER not null, MAJOR VARCHAR2(30), MAJOR VARCHAR2(30), CURRENT_CREDITS NUMBER,) CURRENT_CREDITS NUMBER,)CREATE TABLE CREATE TABLE major_statsmajor_stats ( ( major VARCHAR2(30), m

5、ajor VARCHAR2(30), total_creditstotal_credits NUMBER, NUMBER, total_studentstotal_students NUMBER); NUMBER); 为了保持表为了保持表major_statsmajor_stats中的数据处于更新状态，创建一中的数据处于更新状态，创建一个每次表个每次表studentsstudents被修改时自动更新表被修改时自动更新表major_statsmajor_stats的触的触发器。发器。CREATE OR REPLACE TRIGGER CREATE OR REPLACE TRIGGER Updat

6、eMajorStatsUpdateMajorStatsAFTER INSERT OR DELETE OR UPDATE ON studentsAFTER INSERT OR DELETE OR UPDATE ON studentsDECLAREDECLARECURSOR CURSOR c_Statisticsc_Statistics IS ISSELECT major, COUNT(SELECT major, COUNT(* *) ) total_studentstotal_students, ,SUM(current_creditsSUM(current_credits) ) total_c

7、reditstotal_creditsFROM studentsFROM studentsGROUP BY major;GROUP BY major;BEGINBEGINDELETE FROM DELETE FROM major_statsmajor_stats; ;FOR FOR v_StatsRecordv_StatsRecord in in c_Statisticsc_Statistics LOOP LOOPINSERT INTO INSERT INTO major_statsmajor_stats (major, (major, total_creditstotal_credits,

8、, total_studentstotal_students) )VALUES (VALUES (v_StatsRecord.majorv_StatsRecord.major, , v_StatsRecord.total_creditsv_StatsRecord.total_credits, ,v_StatsRecord.total_studentsv_StatsRecord.total_students););END LOOP;END LOOP;END END UpdateMajorStatsUpdateMajorStats; ; Oracle8提供的这种替代触发器（提供的这种替代触发器（

9、Instead-of trigger）只能定义在视图上（可以是关系或）只能定义在视图上（可以是关系或对象）。与对象）。与DML触发器不同，触发器不同， DML触发器是触发器是在在DML操作之外运行的，而替代触发器则代操作之外运行的，而替代触发器则代替激发它的替激发它的DML语句运行。替代触发器是行语句运行。替代触发器是行一级的。一级的。create table ROOMS(create table ROOMS(ROOM_ID INTEGER not null,ROOM_ID INTEGER not null,DEPARTMENT VARCHAR2(22),DEPARTMENT VARCHAR2

10、(22),BUILDING VARCHAR2(22)BUILDING VARCHAR2(22) create table CLASS( create table CLASS(CLASS_ID INTEGER,CLASS_ID INTEGER,COURSE INTEGER,COURSE INTEGER,ROOM_ID INTEGER ROOM_ID INTEGER ) )CREATE OR REPLACE VIEW CLASSES_ROOMS ASCREATE OR REPLACE VIEW CLASSES_ROOMS ASSELECT SELECT r.departmentr.departme

11、nt, , r.building,c.room_idr.building,c.room_idFROM rooms r, class cFROM rooms r, class cWHERE WHERE r.room_idr.room_id = = c.room_idc.room_idCREATE or replace TRIGGER CREATE or replace TRIGGER ClassesRoomsInsertClassesRoomsInsertINSTEAD OF INSERT ON INSTEAD OF INSERT ON classes_roomsclasses_roomsDEC

12、LAREDECLAREv_roomIDv_roomID rooms.room_id%TYPErooms.room_id%TYPE; ;BEGINBEGIN- First determine the room ID- First determine the room IDSELECT SELECT room_idroom_idINTO INTO v_roomIDv_roomIDFROM roomsFROM roomsWHERE building = :WHERE building = :new.buildingnew.buildingAND AND room_idroom_id = : = :n

13、ew.room_idnew.room_id; ;- And now update the class- And now update the classUPDATE CLASSESUPDATE CLASSESSET SET room_idroom_id = = v_roomIDv_roomIDWHERE department = :WHERE department = :new.departmentnew.departmentAND course = :AND course = :new.coursenew.course; ;END END ClassesRoomsInsertClassesR

14、oomsInsert; ; Oracle8 i Oracle8 i提供了第三种触发器，这种系统触发器在发提供了第三种触发器，这种系统触发器在发生如数据库启动或关闭等系统事件时激发，而不是在执行生如数据库启动或关闭等系统事件时激发，而不是在执行D M LD M L语句时激发。系统触发器也可以在语句时激发。系统触发器也可以在D D LD D L操作时，如操作时，如表的创建中激发。例如，假设我们要记录对象创建的时间，表的创建中激发。例如，假设我们要记录对象创建的时间，我们可以通过创建下面的表来实现我们可以通过创建下面的表来实现 上述记录功能：上述记录功能：系统触发器CREATE TABLE ddl_

15、creations (user_id VARCHAR2(30),object_type VARCHAR2(20),object_name VARCHAR2(30),object_owner VARCHAR2(30),creation_date DATE)CREATE OR REPLACE TRIGGER LogCreationsAFTER CREATE ON SCHEMABEGININSERT INTO ddl_creations (user_id, object_type, object_name,object_owner, creation_date)VALUES (USER, SYS.D

16、ICTIONARY_OBJ_TYPE, SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYSDATE);END LogCreations;创建触发器的通用语法：create table TEMP_TABLEcreate table TEMP_TABLE( ( NUM_COL NUMBER, NUM_COL NUMBER, CHAR_COL VARCHAR2(100) CHAR_COL VARCHAR2(100) )CREATE SEQUENCE CREATE SEQUENCE trig_seqtrig_seqSTART WITH 1STAR

17、T WITH 1INCREMENT BY 1;INCREMENT BY 1;CREATE OR REPLACE PACKAGE CREATE OR REPLACE PACKAGE TrigPackageTrigPackage AS AS- Global counter for use in the triggers- Global counter for use in the triggersv_Counterv_Counter NUMBER; NUMBER;END END TrigPackageTrigPackage; ;CREATE OR REPLACE TRIGGER CREATE OR

18、 REPLACE TRIGGER ClassesBStatementClassesBStatementBEFORE UPDATE ON classBEFORE UPDATE ON classBEGINBEGIN- Reset the counter first.- Reset the counter first.TrigPackage.v_CounterTrigPackage.v_Counter := 0; := 0;INSERT INTO INSERT INTO temp_tabletemp_table ( (num_colnum_col, , char_colchar_col) )VALU

19、ES (VALUES (trig_seq.NEXTVALtrig_seq.NEXTVAL, ,Before Statement: counter = | Before Statement: counter = | TrigPackage.v_CounterTrigPackage.v_Counter););TrigPackage.v_CounterTrigPackage.v_Counter := := TrigPackage.v_CounterTrigPackage.v_Counter + 1; + 1;END END ClassesBStatementClassesBStatement; ;C

20、REATE OR REPLACE TRIGGER ClassesAStatement1CREATE OR REPLACE TRIGGER ClassesAStatement1AFTER UPDATE ON classAFTER UPDATE ON classBEGINBEGININSERT INTO INSERT INTO temp_tabletemp_table ( (num_colnum_col, , char_colchar_col) )VALUES (VALUES (trig_seq.NEXTVALtrig_seq.NEXTVAL, ,After Statement 1: counte

21、r = | After Statement 1: counter = | TrigPackage.v_CounterTrigPackage.v_Counter););- Increment for the next trigger.- Increment for the next trigger.TrigPackage.v_CounterTrigPackage.v_Counter := := TrigPackage.v_CounterTrigPackage.v_Counter + 1; + 1;END ClassesAStatement1;END ClassesAStatement1;CREA

22、TE OR REPLACE TRIGGER ClassesBRow1BEFORE UPDATE ON classFOR EACH ROWBEGININSERT INTO temp_table (num_col, char_col)VALUES (trig_seq.NEXTVAL,Before Row 1: counter = | TrigPackage.v_Counter);- Increment for the next trigger.TrigPackage.v_Counter := TrigPackage.v_Counter + 1;END ClassesBRow1;CREATE OR

23、REPLACE TRIGGER ClassesBRow2BEFORE UPDATE ON classFOR EACH ROWBEGININSERT INTO temp_table (num_col, char_col)VALUES (trig_seq.NEXTVAL,Before Row 2: counter = | TrigPackage.v_Counter);- Increment for the next trigger.TrigPackage.v_Counter := TrigPackage.v_Counter + 1;END ClassesBRow2;用用FOR EACH ROW F

24、OR EACH ROW 语法；语法；可以用可以用WHENWHEN语法；语法；可以用可以用new.column_namenew.column_name、old.column_nameold.column_name语法；语法；Create or replace trigger temp_air After insert on tempFor each rowWhen (new.n=0)Begin dbms_output.put_line( execute trigger);end 无无FOR EACH ROW 语法；语法； 不可以用不可以用WHEN语法；语法； 不可以用不可以用new.column

25、_name、old.column_name语法；语法； 触发器的组成部分触发器的组成部分 触发器语句触发器语句 触发器主体触发器主体 触发器限制触发器限制 触发器语句触发器语句 指定触发器定时、事件、表名及类型指定触发器定时、事件、表名及类型 触发器主体触发器主体 是是 PL/SQL 块或对过程的调用块或对过程的调用 触发器限制触发器限制 可以通过可以通过 WHEN 子句实现子句实现 触发器的类型触发器的类型 应用程序触发器应用程序触发器 数据库触发器数据库触发器 应用程序触发器应用程序触发器 在应用程序内发生特定在应用程序内发生特定 DML 事件时，将隐式事件时，将隐式触发它触发它 数据库触

26、发器数据库触发器 在表、视图或数据库上发生特定在表、视图或数据库上发生特定 DML 事件时，事件时，将隐式触发它将隐式触发它 在表和视图上定义在表和视图上定义 在数据库或方案上，它也可以充当系统在数据库或方案上，它也可以充当系统触发器触发器 DML 触发器组件触发器组件 触发器定时触发器定时 触发器事件触发器事件 表名表名 触发器类型触发器类型 WHEN 子句子句 触发器主体触发器主体 变异表变异表 当前正由当前正由 DML 语句修改语句修改 声明删除级联（引用完整性约束条件）的影声明删除级联（引用完整性约束条件）的影响响 约束表约束表 对于对于 SQL 语句，触发语句直接读取语句，触发语句直

27、接读取 对于声明的引用完整性约束条件，间接读取对于声明的引用完整性约束条件，间接读取 表的变异或约束只是对会话而言的表的变异或约束只是对会话而言的 INSTEAD OF 触发器触发器 是在视图上而不是在表上定义的是在视图上而不是在表上定义的 提供修改视图的透明方法提供修改视图的透明方法 可以针对视图编写可以针对视图编写 INSERT、UPDATE 及及 DELETE 语句语句 它以不可见方式在后台运行它以不可见方式在后台运行 执行在触发器主体中编写的操作执行在触发器主体中编写的操作 嵌套表上的嵌套表上的 INSTEAD OF 触发器触发器 不能使用不能使用 TABLE 子句直接修改视图中的嵌套

28、列子句直接修改视图中的嵌套列 通过通过 INSTEAD OF 触发器进行修改触发器进行修改 嵌套表上的触发器因嵌套表上的触发器因 DML 操作而触发操作而触发 启用和禁用触发器启用和禁用触发器 语法如下：语法如下：alter trigger enable;alter trigger enable;alter trigger disable;alter trigger disable; 其中，其中，Trigger_nameTrigger_name 是触发器名称。是触发器名称。 删除触发器删除触发器 使用以下语法：使用以下语法：drop trigger ;drop trigger ;其中，其中，t

29、rigger_nametrigger_name 是触发器名称。是触发器名称。 查看有关触发器的信息查看有关触发器的信息 使用下列数据字典视图：使用下列数据字典视图：USER_TRIGGERSALL_TRIGGERSDBA_TRIGGERS 扩展数据库的功能扩展数据库的功能 为为 PL/SQL 提供对提供对 SQL 功能的访问功能的访问 用户用户 SYS 拥有所有程序包拥有所有程序包 是公共同义词是公共同义词 可以由任何用户访问可以由任何用户访问 用户必须具有用户必须具有 EXECUTE 权限，才能访问权限，才能访问过程和函数过程和函数 其中的一些内置程序包如下：其中的一些内置程序包如下： DBMS_STANDARD 提供语言工具提供语言工具 DBMS_ALERT 支持数据库事件的异步通知支持数据库事件的异步通知 DBMS_OUTPUT 允许显示输出结果允许显示输出结果 如果禁用它，则忽略对此程序包的调用如果禁用它，则忽略对此程序包的调用 DBMS_LOB 添加用于处理大型对象的实用过程和函数添加用于处理大型对象的实用过程和函数 DBMS_OUTPUT 中的一些过程中的一些过程 Enable Disable Put Put_line New_line Get_line Get_lines DBMS_LOB append compare copy erase filecl