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

数据库触发器讲师：林强目标本章将讨论：数据库触发器内置程序包2简介触发器类似于函数和过程，它们都是具有声明部分、执行部分和异常处理部分的命名PL/SQL块。像包一样，触发器必须在数据库中以独立对象的身份存储，并且不能与包和块具有本地关系。我们在前两章中已经讲过，过程是显式地通过过程调用从其他块中执行的，同时，过程调用可以传递参数。与之相反,触发器是在事件发生时隐式地运行的，并且触发器不能接收参数。运行触发器的方式叫做激发（firing）触发器，触发事件可以是对数据库表的DML（INSERT、UPDATE或DELETE）操作或某种视图的操作(View)。Oracle8i把触发器功能扩展到了可以激发系统事件，如数据库的启动和关闭，以及某种DDL操作。3触发器可以用于下列情况：

维护在表创建阶段通过声明限制无法实现的复杂完整性限制。

通过记录修改内容和修改者来审计表中的信息。在表内容发生变更时，自动通知其他程序采取相应的处理。在订阅发布环境下，发布有关各种事件的信息。4数据库触发器数据库触发器是PL/SQL块或存储过程是在对关联表执行DML操作时触发的是隐式执行的还可能具有声明部分和异常处理部分5createtableSTUDENTS(

IDINTEGERnotnull,MAJORVARCHAR2(30),CURRENT_CREDITSNUMBER,)CREATETABLEmajor_stats(majorVARCHAR2(30),

total_creditsNUMBER,

total_studentsNUMBER);为了保持表major_stats中的数据处于更新状态，创建一个每次表students被修改时自动更新表major_stats的触发器。例子2/1DML触发6例子2/2DML触发CREATEORREPLACETRIGGERUpdateMajorStatsAFTERINSERTORDELETEORUPDATEONstudentsDECLARECURSORc_StatisticsISSELECTmajor,COUNT(*)total_students,SUM(current_credits)total_creditsFROMstudentsGROUPBYmajor;BEGINDELETEFROMmajor_stats;FORv_StatsRecordinc_StatisticsLOOPINSERTINTOmajor_stats(major,total_credits,total_students)VALUES(v_StatsRecord.major,v_StatsRecord.total_credits,v_StatsRecord.total_students);ENDLOOP;ENDUpdateMajorStats;7替代触发器Oracle8提供的这种替代触发器（Instead-oftrigger）只能定义在视图上（可以是关系或对象）。与DML触发器不同，DML触发器是在DML操作之外运行的，而替代触发器则代替激发它的DML语句运行。替代触发器是行一级的。8例子替代触发器createtableROOMS(ROOM_IDINTEGERnotnull,DEPARTMENTVARCHAR2(22),BUILDINGVARCHAR2(22))createtableCLASS(

CLASS_IDINTEGER,

COURSEINTEGER,

ROOM_IDINTEGER

)9视图：CREATEORREPLACEVIEWCLASSES_ROOMSAS

SELECTr.department,r.building,c.room_id

FROMroomsr,classc

WHEREr.room_id=c.room_id

CREATEorreplaceTRIGGERClassesRoomsInsert

INSTEADOFINSERTONclasses_rooms

DECLARE

v_roomID

rooms.room_id%TYPE;

BEGIN

--FirstdeterminetheroomID

SELECTroom_id

INTOv_roomID

FROMrooms

WHEREbuilding=:new.building

ANDroom_id=:new.room_id;

--Andnowupdatetheclass

UPDATECLASSES

SETroom_id=v_roomID

WHEREdepartment=:new.department

ANDcourse=:new.course;

ENDClassesRoomsInsert;

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

TABLE

ddl_creations(

user_id

VARCHAR2(30),

object_type

VARCHAR2(20),

object_name

VARCHAR2(30),

object_owner

VARCHAR2(30),

creation_date

DATE)CREATE

OR

REPLACE

TRIGGER

LogCreations

AFTER

CREATE

ON

SCHEMA

BEGIN

INSERT

INTO

ddl_creations(user_id,object_type,object_name,

object_owner,creation_date)

VALUES(USER,SYS.DICTIONARY_OBJ_TYPE,SYS.DICTIONARY_OBJ_NAME,

SYS.DICTIONARY_OBJ_OWNER,SYSDATE);

END

LogCreations;12创建触发器的通用语法：

13createtableTEMP_TABLE

(

NUM_COLNUMBER,

CHAR_COLVARCHAR2(100)

)CREATESEQUENCEtrig_seqSTARTWITH1INCREMENTBY1;CREATEORREPLACEPACKAGETrigPackageAS

--Globalcounterforuseinthetriggers

v_CounterNUMBER;ENDTrigPackage;

14CREATEORREPLACETRIGGERClassesBStatement

BEFOREUPDATEONclass

BEGIN

--Resetthecounterfirst.

TrigPackage.v_Counter:=0;

INSERTINTOtemp_table(num_col,char_col)

VALUES(trig_seq.NEXTVAL,

'BeforeStatement:counter='||TrigPackage.v_Counter);

TrigPackage.v_Counter:=TrigPackage.v_Counter+1;

ENDClassesBStatement;CREATEORREPLACETRIGGERClassesAStatement1AFTERUPDATEONclassBEGININSERTINTOtemp_table(num_col,char_col)VALUES(trig_seq.NEXTVAL,'AfterStatement1:counter='||TrigPackage.v_Counter);--Incrementforthenexttrigger.TrigPackage.v_Counter:=TrigPackage.v_Counter+1;ENDClassesAStatement1;15CREATEORREPLACETRIGGERClassesBRow1BEFOREUPDATEONclassFOREACHROWBEGININSERTINTOtemp_table(num_col,char_col)VALUES(trig_seq.NEXTVAL,'BeforeRow1:counter='||TrigPackage.v_Counter);--Incrementforthenexttrigger.TrigPackage.v_Counter:=TrigPackage.v_Counter+1;ENDClassesBRow1;CREATEORREPLACETRIGGERClassesBRow2

BEFOREUPDATEONclass

FOREACHROW

BEGIN

INSERTINTOtemp_table(num_col,char_col)

VALUES(trig_seq.NEXTVAL,

'BeforeRow2:counter='||TrigPackage.v_Counter);

--Incrementforthenexttrigger.

TrigPackage.v_Counter:=TrigPackage.v_Counter+1;

ENDClassesBRow2;16行触发器用FOREACHROW语法；可以用WHEN语法；可以用new.column_name、old.column_name语法；Createorreplacetriggertemp_air

AfterinsertontempForeachrowWhen(new.n=0)Begin

dbms_output.put_line(‘executetrigger’);end17语句触发器无FOREACHROW语法；不可以用WHEN语法；不可以用new.column_name、old.column_name语法；18数据库触发器10-2触发器的组成部分触发器语句触发器主体触发器限制19数据库触发器10-3触发器语句指定触发器定时、事件、表名及类型触发器主体是PL/SQL块或对过程的调用触发器限制可以通过WHEN子句实现20数据库触发器10-4触发器的类型应用程序触发器数据库触发器21数据库触发器10-5应用程序触发器在应用程序内发生特定DML事件时，将隐式

触发它数据库触发器在表、视图或数据库上发生特定DML事件时，将隐式触发它在表和视图上定义在数据库或方案上，它也可以充当系统

触发器22数据库触发器10-6DML触发器组件触发器定时触发器事件表名触发器类型WHEN子句触发器主体23数据库触发器10-7变异表当前正由DML语句修改声明删除级联（引用完整性约束条件）的影响约束表对于SQL语句，触发语句直接读取对于声明的引用完整性约束条件，间接读取表的变异或约束只是对会话而言的24INSTEADOF触发器是在视图上而不是在表上定义的提供修改视图的透明方法可以针对视图编写INSERT、UPDATE及DELETE语句它以不可见方式在后台运行执行在触发器主体中编写的操作数据库触发器10-825嵌套表上的INSTEADOF触发器不能使用TABLE子句直接修改视图中的嵌套列通过INSTEADOF触发器进行修改嵌套表上的触发器因DML操作而触发启用和禁用触发器语法如下：altertrigger<Trigger_name>enable;altertrigger<Trigger_name>disable;

其中，Trigger_name

是触发器名称。数据库触发器10-926删除触发器使用以下语法：droptrigger<trigger_name>;其中，trigger_name

是触发器名称。查看有关触发器的信息使用下列数据字典视图：USER_TRIGGERSALL_TRIGGERSDBA_TRIGGERS数据库触发器10-1027扩展数据库的功能为PL/SQL提供对SQL功能的访问用户SYS拥有所有程序包是公共同义词可以由任何用户访问用户必须具有EXECUTE权限，才能访问过程和函数内置程序包6-128其中的一些内置程序包如下：

内置程序包6-229DBMS_STANDARD提供语言工具DBMS_ALERT支持数据库事件的异步通知DBMS_OUTPUT允许显示输出结果如果禁用它，则忽略对此程序包的调用DBMS_LOB添加用于处理大型对象的实用过程和函数内置程序包6-330DBMS_OUTPUT中的一些过程EnableDisablePutPut_lineNew_lineGet_lineGet_lines

内置程序包6-431DBMS_LOBappendcomparecopyerasefileclosefileexistsfilegetnamegetlength内置程序包6-532Oracle9i中新增程序包的部分列表内置程序包6-6程序包名称描述DBMS_AQELM提供过程管理AdvancedQueuing异步通知的配置（通过电子邮件和HTTP）

。DBMS_FGA提供细粒度安全函数。DB