第25讲触发器的类型,建立,修改,删除,应用

1、第25讲触发器的类型，建立，修改，删除，应用目的：1. 掌握触发器的概念，类型，作用2. 掌握各种类型触发器的创建，修改，删除3. 掌握编写复杂触发器的应用。授课内容：1触发器概念触发器是一种特殊的pl/sql程序块，它不用我们显式调用它，不像存储过程，函数， 需要我们显式调用它，当某个条件或事件发生时，触发器自动运行。2.创建，修改，删除触发器权限创建触发器，要求用户必须有更改表的权限，alter any table权限，以及create any trigger权限，为了在其他方案屮创建触发器，用户必须具有create any trigger 权限。修改触发器必须具有alter any tr

2、igger权限。3触发器类型：1) 行级触发器(row level trigger):最常用的触发器。对dml语句中影响的每一行执行一次。在create trigger语句中使用for each row 子句建立行级触发器。2) 语句级触发器(statement level trigger)dml语句运行才执行一次，语句级触发器是使用create trigger创建时默认的触发器类型。语句级触发器一般不常用于与数据相关的活动。通常用于强制实施一个能在一个表上执行各种操作的额外安全措施。如：一条insert语句在emp表上插入了 100条记录，但这表的语句级触发器只执 行一次。3) before

3、触发器在 insert, update, delete z前运行。可以引用dml影响记录的i口值和新值。i口值表示在dml语句执行之前的数据。新值表示在dml语句更新后的表的数据。旧值用：old.字段名新值用：new.字段名4) after触发器：在insert, update, delete 语句之后运行。可以引用dml影响记录的旧值和新值。旧值用：old字段名新值用：new.字段名在审计应用程序中经常使用after行级触发器，因为直到行被修改才触发这些触 发器。行的成功修改表明数据己经通过表的各种约朿。5）instead of 触发器：使用instead of触发器，告诉oracle要作的

4、事情，而不是执行调用触发器的操 作。6）模式触发器（schema trigger）可以在模式级操作上建立触发器,如:create table, alter table, drop table, rename, revoke, grant等操作上建立触发器。模式级触发器的作用为：阻止执行ddl操作 或发生ddl操作吋进行额外的安全 监控。7）数据库级触发器（database level trigger）可以在数据库级事件上建立触发器，如错误，注册，注销，关闭，启动等事件。 使用数据库级触发器进行自动的数据库维护，或审计活动。4. 触发器创建和修改语法：（只给出最常用的触发器类型定义）create

5、 or replace trigger lschema_name.jtrigger_name before | after | instead of delete | insert | update of column_name, . f or delete | insert | update of column, . on table_namefor each row when condition declare变量定义；begin语句；q o qend;1）触发器的名字在方案下必须唯一。不能与表，视图，存储过程，函数等同名。2）触发器的名字必须吋合法的oracle标识，长度为30字符以内。

6、3）使用for each row为行级触发器，否则为语句级触发器。4）使用when子句进一步限定触发器执行，当满足条件时才执行触发器。5）when子句的条件中可以使用新值，旧值。在when条件中引用新值和ih值不用 使用：old,:new,直接使用new.字段名，old.字段名。6）在pl/sql中使用新值和旧值，必须使用 冒号开头的new和old.:new,: oldo7) 触发器中的事务处理类型为：inserting, deleting, updating.当在一个触发器屮同时 使用2种以上的dml语句时，应该判断执行的是什么操作。如果是insert语句， 则inserting为true,

7、如果是update语句，则updating为true,如果执行 delete语句，则deleting为true. 般使用if inserting then语句进行判断。8) 不要在触发器中编写大段的代码，应该将代码写在存储过程中，在触发器中调用存 储过程。call存储过程名(参数,oooo)；最简单的触发器创建语法：create or replace trigger document_insert_eventafter inserton documentfor each rowdeclarebeginend;触发器创建的例子：create or replace trigger trigger_

8、001before updateon empfor each rowwhen (new.sal>old.sal)declarebegininsert into temp001 (no, name) values(:old.ename, :old.sal);end;5. 创建ddl级触发器：1) 建立在ddl操作上的触发器。2) 可以利用ddl事件触发器来执行在簇，函数，索引，包，过程，角色，序列，同 义词，表，表空间，触发器，类型，用户，视图上执行create, alter和drop命令吋 的执行代码。3)创建ddl级触发器的例子：create or replace trigger tt

9、02after create on schemabegincall insert_audit_record(ora.dict_obj_name);end;6. 创建数据库级的触发器table 16-1 system defined event attributesattributetypedescriptionexampleora_cliep_addressvarchar2returns the tp address of the client in a logon event, when the underlying protocol is tcp/ipif (ora_sysevent =

10、'logon')then addr := ora_client_ip_address; end if;ora database namevarchar2(50)database name.declaredb_name varchar2(50); begindb_name := ora_dcitabase_ncime; end;ora_des_encrypted_passwordvarchar2the des encrypted password of the user being created or altered.if (ora_dict_obj_type = 'u

11、ser')then insert intoevent_table(ora_dcs cncrypted passw ord);end if;ora_dict_obj_namevarchar(30)name of the dietioneiry object on which the ddl operation occurred.insert into event_table c changed object is ' ora diet obj name");ora diet obj name list (nameist out ora. name list t)mmmm

12、btnary_tntegerreturn the list of object neimes of objeets being modified in the event.if (ora_sysevent ='associatestatistics，)then numberjnodified := ora diet obj name list (name_list);end if;ora. diet obj ownervarchar(30)owner of the dictionary objectinsert into event tabic ('objectattribut

13、etypedescriptionexampleon whi ch the ddl operation occurred.owner is'ora_dict_obj_owncr，);oradictobjownerlist(owne r_list out ora_name_list_t)binaryintegerreturns the list of object owners of objects being modified in the event.if (ora_sysevent ='associate statistics，)thennumber_of_modified_

14、objec ts :=orad i c t_ob j _own er_list( owner_li st);end if;ora_dict_obj_typevarchar(20)type of the dictionary object on whi ch the ddl operation occurred.insert into eventtable ('this object is a ' ora_dict_obj_typc);ora_grantee( user_listout ora_ncime_list_t)binaryintegerreturns the grant

15、ees of a gra nt eve nt in the out paramctcr; returns the number of grantees in the return value.if (ora_sysevent = 'grant') thennumber_of_users := ora_grantcc(uscr_list); end if;ora_ins tanccjnuninumberinstancc number.if (ora_instance_num 二 1)then insert intoevent table(i)；end if;ora_is_altc

16、r_column( column_name in varchar2)booleanrcturns true if the specified column is altered.if (ora_sysevent 二 'alter' and orad i ct_obj_type ='table，)then alter_column := ora is alter columnc foo ');end if;ora_is_creatingnested_tablebooleanreturn true if theif (ora_sysevent 二attributet

17、ypedescriptionexamplecurrent event is crcating a ncsted table'create' andora_dict_obj_type 二 'table' and ora_is_creating_nested_t able)then insert intocvcnt_tabvalues c a nested table is created，);end if;ora_i s_drop_co1umn( column_namc in varciiar2)booleanreturns true if the specifi

18、ed column is dropped.if (ora_sysevent = 'alter' and ora_dict_obj_type = 'table')then dropcolumn :二 ora is drop column c foo"end if;ora_is_servererrorbooleanreturns true if given error is on error stack, false othcrwisc.if(ora_i s_servererrorerro r number)ythen insert intoevent_t

19、ablec server error!');end if;ora_login_uservarciiar2(30)login user name.select ora_login_uscr from dual;ora_partitionposbinaryintegerin an instead of trigger for create table, the position within the sql text where you could insert a partition clause.- retrieve ora_sql_txt into一一 sql_text variab

20、le first.n :二 ora_partition_pos; new_stmt :二substr(sql_text, 1, nl)my_partition_clause''substr (sql text,n);-attributetypedescriptionexampleora_privilege_list( privilcgc_list out ora name list t)binary.integerreturns the 1ist of privileges being granted by the grantee or the list of privileg

21、es revoked from the revokee in the out parameter; returns the number of privileges in the return value.if (ora_sysevent = 'grant' or ora_sysevent = 'revoke') thennumber_of_privileges :二 ora_privilege_list(priv_ list);end if;ora_revokee ( userlist out ora_ncime_l i st_t)binaryintegerr

22、eturns the revokees of a revoke event in the out parameter;returns the number of revokees in the return value. if (ora_sysevent ='revoke，)thennumber_of_users :二 ora_revokee(user_li st);ora_server_errornumbergiven a position (1 for top of stack), it returns the error number at that position on er

23、ror stackinsert into event_table ('topstack error ' ora_server_error(1);ora_server_error_depthbinary.integerreturns the total number of error messages on the error stack.n :二 ora_server_error_depth; 一一 this value is used with 一一 other functions such as ora_server_errorora_server_error_msg(po

24、sition in binary_intcger)varchar2given a position (1 for top of stack), it returns the error message at that position on errorinsert into event_table ('top stack error message" ora_server_error_msg(1)attributetypedescriptionexamplestackora server error num params (position in eger

25、)binary_integergiven a position (1 for top of stack), it returns the number of strings that have been substituted into the error message using a format like sn :二ora server error num par ams (1);ora_scrvcr_crror_param(position in eger, param in binaryinteger)varciiar2given a position (1 fo

26、r top of stack) and a parameter number, returns the matching s， ct, and so on substitution value in the error message.- e. g. the 2rd %s in a message一 like "expected %s, found %sparam :二 ora_scrvcr_crror_param(1 ,2);ora_sql_txt (sql_tcxt out ora name list t)binary_integerreturns the sql text of

27、 the triggering statement in the out parameter. tf the statement is long, it is broken up into multiple pl/sql table elements. the function return value specifies how many elements are in the pl/sql tab le.sql_tcxt ora name list t;stmt varchar2 (2000); n :二ora_sql_txt(sql_tcxt);for i in l.n loopstmt

28、 :二 stmt sql_text(i);end loop;insert into event_table ('textof triggering statement: ，丨丨stmt);ora_syscventvarciiar2 (20)system event firing the trigger: event name is same as that in theinsert into event_table (ora_sysevent);attributetypedescriptionexamplesyntax.ora_with_grant_opt i onbooleanrct

29、urns true if the privileges are granted with grant option.if (ora_sysevent 二 'grant' and ora_wi th_grant opt i on = true)then insert into cvcnt_table c with grant option)； end if;space_error_info(booleanreturns true ififerror_number out number,the error is(space_error_info (eno,crror_type ou

30、t varciiar2,rclated to antyp,object_owner out varchar2,out-of-spaceowner, ts, obj, subobj)=tab1e_space_name outcondition, andtrue)varchar2,objectname out varchar2,fills in the out parameters wi ththe nsub_object_name out varchar2)information about the object that caused the error.dbms_output. put_li

31、ncc th eobject 'obj' ownedby，| owner' has run outofspace.，);end if;创建数据库级触发器的例子：create or replace trigger tt003_on_database after startup on databasebeginnull;end;client eventsclient events are the events related to user logon/logoff, dml, and ddl operations. for example:create or replac

32、e trigger on_logonafter logonon the_user. schemabegindo something;end;7. 触发器删除语法：drop trigger trigger_name触发器删除例子：drop trigger tt0018. 触发器的禁止和激活使用 alter trigger trigger_name disable 来禁止触发器 如：alter trigger tt003 disable使用alter table table_name disable all triggers来禁止表的所有的触发器。 女口: alter table emp disable all triggers9. 触发器使用：触发器是在执行特定的dml语句时自动运行的，不碍要像存储过程，函