第25讲 触发器的类型建立修改删除应用Word文件下载.docx

1、4） AFTER 触发器：在insert, update, delete 语句之后运行。OLD.字段名NEW.字段名在审计应用程序中经常使用AFTER行级触发器，因为直到行被修改才触发这些触发器。行的成功修改表明数据已经通过表的各种约束。5） INSTEAD OF触发器：使用INSTEAD OF 触发器，告诉Oracle要作的事情，而不是执行调用触发器的操作。6） 模式触发器 （Schema trigger）可以在模式级操作上建立触发器，如：create table, alter table, drop table, rename, revoke, grant 等操作上建立触发器。模式级触发器

2、的作用为： 阻止执行DDL操作 或发生DDL操作时进行额外的安全监控。7） 数据库级触发器 （Database Level Trigger ）可以在数据库级事件上建立触发器，如错误，注册，注销，关闭，启动等事件。使用数据库级触发器进行自动的数据库维护，或审计活动。4. 触发器创建和修改语法：（只给出最常用的触发器类型定义）CREATE OR REPLACE TRIGGER schema_name.trigger_name BEFORE | AFTER | INSTEAD OF delete | insert | update of column_name, or delete | insert

3、 | update of column, . ON table_name FOR EACH ROW WHEN condition DECLARE 变量定义；BEGIN 语句； 。END;1） 触发器的名字在方案下必须唯一。不能与表，视图，存储过程，函数等同名。2） 触发器的名字必须时合法的Oracle标识，长度为30字符以内。3） 使用FOR EACH ROW为行级触发器，否则为语句级触发器。4） 使用WHEN 子句进一步限定触发器执行，当满足条件时才执行触发器。5） WHEN子句的条件中可以使用新值，旧值。在WHEN 条件中引用新值和旧值不用使用 :OLD, :NEW, 直接使用 NEW.字

4、段名， OLD.字段名。6） 在PL/SQL中使用新值和旧值，必须使用 冒号开头的NEW 和OLD. ：NEW， ：OLD。7） 触发器中的事务处理类型为： inserting, deleting, updating. 当在一个触发器中同时使用2种以上的DML语句时，应该判断执行的是什么操作。如果是insert 语句，则 INSERTING为true, 如果是update语句，则UPDATING为true, 如果执行DELETE语句，则DELETING为true. 一般使用 IF inserting THEN 语句进行判断。8） 不要在触发器中编写大段的代码，应该将代码写在存储过程中，在触发器

5、中调用存储过程。 Call 存储过程名（参数，。）；最简单的触发器创建语法：create or replace trigger document_insert_eventafter insert on Documentfor each rowdeclarebeginend;触发器创建的例子：create or replace trigger Trigger_001before updateon empwhen （new.salold.sal） insert into TEMP001 （no, name） values（:old.ename, :old.sal）;5. 创建DDL级触发器：1）

6、建立在DDL操作上的触发器。2） 可以利用DDL事件触发器来执行在簇，函数，索引，包，过程，角色，序列，同义词，表，表空间，触发器，类型，用户，视图上执行create, alter 和drop命令时的执行代码。3） 创建DDL级触发器的例子：create or replace trigger TT02after create on schema call insert_audit_record（ora.dict_obj_name）;6. 创建数据库级的触发器 Table 16-1 System Defined Event Attributes AttributeTypeDescriptionE

7、xampleora_client_ip_addressVARCHAR2Returns the IP address of the client in a LOGON event, when the underlying protocol is TCP/IPif （ora_sysevent = LOGON） then addr := ora_client_ip_address;end if;ora_database_nameVARCHAR2（50）Database name.DECLARE db_name VARCHAR2（50）; db_name := ora_database_name;or

8、a_des_encrypted_passwordThe DES encrypted password of the user being created or altered.IF （ora_dict_obj_type = USER THEN INSERT INTO event_table（ora_des_encrypted_password）;END IF;ora_dict_obj_nameVARCHAR（30）Name of the dictionary object on which the DDL operation occurred.INSERT INTO event_table （

9、Changed object is | ora_dict_obj_name）;ora_dict_obj_name_list （name_list OUT ora_name_list_t）BINARY_INTEGERReturn the list of object names of objects being modified in the event.ASSOCIATE STATISTICS then number_modified :（name_list）;ora_dict_obj_ownerOwner of the dictionary object on which the DDL o

10、peration occurred.INSERT INTO event_table （object owner isora_dict_obj_ownerora_dict_obj_owner_list（owner_list OUT ora_name_list_t）Returns the list of object owners of objects being modified in the event. then number_of_modified_objects :ora_dict_obj_owner_list（owner_list）;ora_dict_obj_typeVARCHAR（2

11、0）Type of the dictionary object on which the DDL operation occurred.This object is a ora_dict_obj_type）;ora_grantee（ user_list OUT ora_name_list_t）Returns the grantees of a grant event in the OUT parameter; returns the number of grantees in the return value.GRANT） thennumber_of_users :ora_grantee（us

12、er_list）;ora_instance_numNUMBERInstance number.IF （ora_instance_num = 1） THEN INSERT INTO event_table 1ora_is_alter_column（ column_name IN VARCHAR2）BOOLEANReturns true if the specified column is altered.ALTER andora_dict_obj_type = TABLE then alter_column :ora_is_alter_column（FOOora_is_creating_nest

13、ed_tableReturn TRUE if the current event is creating a nested tableCREATE and ora_is_creating_nested_table） then insert into event_tab values （A nested table is createdora_is_drop_column（ Returns true if the specified column is dropped. then drop_column :ora_is_drop_column（ora_is_servererrorReturns

14、TRUE if given error is on error stack, FALSE otherwise.IF （ora_is_servererror（error_number）Server error!ora_login_userVARCHAR2（30）Login user name.SELECT ora_login_user FROM dual;ora_partition_posIn an INSTEAD OF trigger for CREATE TABLE, the position within the SQL text where you could insert a PART

15、ITION clause.- Retrieve ora_sql_txt into- sql_text variable first.n := ora_partition_pos;new_stmt :substr（sql_text, 1, n-1） | | my_partition_clause | | substr（sql_text, n）;ora_privilege_list（privilege_list OUT Returns the list of privileges being granted by the grantee or the list of privileges revo

16、ked from the revokee in the OUT parameter; returns the number of privileges in the return value. or ora_sysevent = REVOKE then number_of_privileges :ora_privilege_list（priv_list）;ora_revokee （user_list OUT Returns the revokees of a revoke event in the OUT parameter; returns the number of revokees in

17、 the return value.） thenora_revokee（user_list）;ora_server_errorGiven a position （1 for top of stack）, it returns the error number at that position on error stacktop stack error ora_server_error（1）;ora_server_error_depthReturns the total number of error messages on the error stack.= ora_server_error_

18、depth;- This value is used with- other functions such as- ora_server_errorora_server_error_msg （position in binary_integer）Given a position （1 for top of stack）, it returns the error message at that position on error stackstack error messageora_server_error_msg（1）;ora_server_error_num_params Given a

19、 position （1 for top of stack）, it returns the number of strings that have been substituted into the error message using a format like %s.ora_server_error_num_params（1）;ora_server_error_param （position in binary_integer, param in binary_integer）Given a position （1 for top of stack） and a parameter n

20、umber, returns the matching , %d, and so on substitution value in the error message.- E.g. the 2rd %s in a message- like Expected %s, found %sparam :ora_server_error_param（1,2）;ora_sql_txt （sql_text out Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long,

21、it is broken up into multiple PL/SQL table elements. The function return value specifies how many elements are in the PL/SQL table.sql_text ora_name_list_t;stmt VARCHAR2（2000）;.= ora_sql_txt（sql_text）;FOR i IN 1.n LOOP stmt := stmt | sql_text（i）;END LOOP;text of triggering statement:stmt）;ora_syseve

22、ntVARCHAR2（20）System event firing the trigger: Event name is same as that in the syntax.（ora_sysevent）;ora_with_grant_optionReturns true if the privileges are granted with grant option.ora_with_grant_option = TRUE） then insert into event_table with grant optionspace_error_info（error_number OUT NUMBE

23、R,error_type OUT VARCHAR2,object_owner OUT VARCHAR2,table_space_name OUT VARCHAR2,object_name OUT VARCHAR2,sub_object_name OUT VARCHAR2）Returns true if the error is related to an out-of-space condition, and fills in the OUT parameters with information about the object that caused the error.if （space

24、_error_info（eno, typ, owner, ts, obj, subobj） = TRUE） dbms_output.put_line（The object | obj | owned by | owner | has run out of space.创建数据库级触发器的例子：create or replace trigger TT003_ON_DATABASEafter startup on database null;Client EventsClient events are the events related to user logon/logoff, DML, and DDL operations. For example:CREATE OR REPLACE TRIGGER On_Logon AFTER LOGON ON The_user.Schema BEGIN Do_Something;7. 触发器删除DROP TRIGGER trigger_name触发器删除例子：drop trigger TT0018.