SQL 触发器（trigger）学习笔记

1、触发器的概念触发器也是一种带名的PL/SQL块。触发器类似于过程和函数，因为它们都是拥有声明、执行和异常处理过程的带名PL/SQL块。与包类似，触发器必须存储在数据库中并且不能被块进行本地化声明。对于触发器而言，当触发事件发生的时候就会显式地执行该触发器，并且触发器不接受参数。创建触发器的语法如下view plaincopy to clipboardprint？CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER | INSTEAD OF} triggering_event[referencing_clause][WHEN trigger_condition][FOR EACH ROW]Trigger_body;CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER | INSTEAD OF} triggering_event[referencing_clause][WHEN trigger_condition][FOR EACH ROW]Trigger_body;其中referencing_clause子句的用途是通过一个不同的名称，引用当前正在被更新的记录行中的数据。WHEN子句中的trigger_condition—如果出现—就应该首先执行判断，只有当这个条件值为真的时候，才会执行触发器的主体代码。2、DML触发器的激活顺序1）执行before语句级触发器—如果存在这种触发器2）对受该语句影响的每一行记录执行before行级触发器—如果存在这种触发器执行该语句本身执行after行级触发器--如果存在这种触发器3）执行after语句级触发器--如果存在这种触发器同一种类型的触发器的点火次序没有经过定义。如果该次序很重要的话，那么建议将所有这些操作组合到一个触发器当中。3、行级触发器中的关联标识符触发器的激活语句每处理一行数据，行级触发器就会激活一次。可以在这种行级触发器内部，访问正被处理的记录行中的数据。这是通过两个关联标识符--:old和:new—实现的。关联标识符也是PL/SQL的一种特殊的绑定变量。标识符前面的冒号，既说明这二者都是绑定变量，同时也说明它们不是一般的PL/SQL变量。PL/SQL编译器会将它们看作下面这个类型的记录：Triggering_table%ROWTYPE其中triggering_table是在其上定义触发器的表名。于是，下面这种引用:new.field就只有当其中的field是该触发表中的字段名时才会有效。触发语句:old:newINSERT未定义—所有字段均为NULL触发语句完成的时候，要插入的值UPDATE更新以前相应记录行的原始值触发语句完成的时候，要更新的值DELETE删除以前相应记录行的原始值未定义—所有字段均为NULL注意：INSERT语句上没有定义:old标识符，DELETE语句上也没有定义:new标识符。如果再INSERT语句上使用:old标识符，或者在DELETE语句上使用:new标识符，PL/SQL并不会产生错误，但是这两个字段值都会为NULL。伪记录虽然在语法构成上，会将:new和:old看作triggering_table%ROWTYPE类型的记录，但是，实际上它们并不是记录。因此，那些能够在记录上正常执行的操作，并不能在:new和:old上执行。例如，不能将它们作为一个整体进行赋值。只能对其中的各个字段分别赋值。view plaincopy to clipboardprint？CREATE OR REPLACE TRIGGER TempDeleteBEFORE DELETE ON temp_tableFOR EACH ROWDECLAREv_TempRec temp_table%ROWTYPE;BEGIN/* This is not a legal assignment, since :old is not trulya record. */v_TempRec := :old;/* We can accomplish the same thing, however, by assigningthe fields individually. */v_TempRec.char_col := :old.char_col;v_TempRec.num_col := :old.num_col;END TempDelete;/CREATE OR REPLACE TRIGGER TempDeleteBEFORE DELETE ON temp_tableFOR EACH ROWDECLAREv_TempRec temp_table%ROWTYPE;BEGIN/* This is not a legal assignment, since :old is not trulya record. */v_TempRec := :old;/* We can accomplish the same thing, however, by assigningthe fields individually. */v_TempRec.char_col := :old.char_col;v_TempRec.num_col := :old.num_col;END TempDelete;/REFERENCING子句还可以使用REFERENCING子句，为:old和:new换一个不同的名称。该子句出现在触发事件以后，WHEN子句以前。其语法如下：REFERENCING [OLD AS old_name] [NEW AS new_name]在触发器主体中，可以使用:old_name和:new_name分别代替:old和:new。注意，在REFERENCING子句中关联标识符都不带冒号。如下面这个例子所示view plaincopy to clipboardprint？CREATE OR REPLACE TRIGGER GenerateAuthorIDBEFORE INSERT OR UPDATE ON authorsREFERENCING new AS new_authorFOR EACH ROWBEGIN/* Fill in the ID field of authors with the next value fromauthor_sequence. Since ID is a column in authors, :new.IDis a valid reference. */SELECT author_sequence.NEXTVALINTO :new_author.IDFROM dual;END GenerateAuthorID;/CREATE OR REPLACE TRIGGER GenerateAuthorIDBEFORE INSERT OR UPDATE ON authorsREFERENCING new AS new_authorFOR EACH ROWBEGIN/* Fill in the ID field of authors with the next value fromauthor_sequence. Since ID is a column in authors, :new.IDis a valid reference. */SELECT author_sequence.NEXTVALINTO :new_author.IDFROM dual;END GenerateAuthorID;/4、WHEN子句WHEN子句只能在行级触发器中使用。如果在行级触发器的定义中给出了WHEN子句，触发器主体就只对满足WHEN所定义条件的那些记录行执行。WHEN子句的基本形式如下：WHEN trigger_condition其中，trigger_condition是一个布尔表达式。每处理一行记录，都会重新判断该表达式的值。也可以在trigger_condition内部使用:new和:old记录，但是与REFERENCING子句一样，在trigger_condition内部使用:new和:old时，不需要冒号。仅在触发器主体中才需要使用冒号。5、触发器谓词可以在触发器内部使用3个布尔函数，判断触发该触发器的到底是什么操作。这3个谓词分别是INSERTING、UPDATING和DELETING.使用方法如下面这个例子所示view plaincopy to clipboardprint？CREATE OR REPLACE TRIGGER LogInventoryChangesBEFORE INSERT OR DELETE OR UPDATE ON inventoryFOR EACH ROWDECLAREv_ChangeType CHAR（1）;BEGIN/* Use "I" for an INSERT, "D" for DELETE, and "U" for UPDATE. */IF INSERTING THENv_ChangeType := "I";ELSIF UPDATING THENv_ChangeType := "U";ELSEv_ChangeType := "D";END IF;/* Record all the changes made to inventory ininventory_audit. Use SYSDATE to generate the timestamp, andUSER to return the userid of the current user. */INSERT INTO inventory_audit（change_type, changed_by, timestamp,old_isbn, old_status, old_status_date, old_amount,new_isbn, new_status, new_status_date, new_amount）VALUES（v_ChangeType, USER, SYSDATE,:old.isbn, :old.status, :old.status_date, :old.amount,:new.isbn, :new.status, :new.status_date, :new.amount）;END LogInventoryChanges;/CREATE OR REPLACE TRIGGER LogInventoryChangesBEFORE INSERT OR DELETE OR UPDATE ON inventoryFOR EACH ROWDECLAREv_ChangeType CHAR（1）;BEGIN/* Use "I" for an INSERT, "D" for DELETE, and "U" for UPDATE. */IF INSERTING THENv_ChangeType := "I";ELSIF UPDATING THENv_ChangeType := "U";ELSEv_ChangeType := "D";END IF;/* Record all the changes made to inventory ininventory_audit. Use SYSDATE to generate the timestamp, andUSER to return the userid of the current user. */INSERT INTO inventory_audit（change_type, changed_by, timestamp,old_isbn, old_status, old_status_date, old_amount,new_isbn, new_status, new_status_date, new_amount）VALUES（v_ChangeType, USER, SYSDATE,:old.isbn, :old.status, :old.status_date, :old.amount,:new.isbn, :new.status, :new.status_date, :new.amount）;END LogInventoryChanges;/6、INSTEAD-OF触发器INSTEAD-OF触发器仅可以定义在视图上（关系型的或对象），并且它们可以替代点火它们的DML语句进行点火。INSTEAD-OF触发器必须是行级的。7、触发器的限制触发器的主体是一个PL/SQL块。在PL/SQL块中可以使用的所有语句在触发器主体中都是合法的，但是要受到下面限制的约束：触发器不应该使用事务控制语句—COMMIT、ROLLBACK或SAVEPOINT。触发器作为触发语句执行的一部分被点火，它和触发语句在同一个事务中。当触发语句被提交或撤回提交时，触发器的工作也相应被提交会撤回提交。由触发器主体调用的任何过程和函数都不能使用事务控制语句。触发器主体不能声明任何LONG或者LONG RAW变量。而且，:new和:old不能指向定义触发器的表中的LONG和LONG RAW列。触发器主体可以访问的表有所限制。触发器P-Code当包或者子程序存储在数据字典中时，存储的除了该对象的源代码还有经过编译的p-code。但是对于触发器来说就不是这样的。在数据字典中唯一存储的是触发器的源代码，而不是p-code。结果，每次当从数据字典中重新读出触发器时，必须要进行编译。这对触发器的定义和使用的方式不会带来什么影响，但是会影响触发器的性能。8、系统触发器我们前面所看到的DML触发器和INSTEAD-OF触发器都是基于DML事件。而另一方面，系统触发器的激活则是基于两种不同的事件：DDL事件或数据库事件。DDL事件包括CREATE、ALTER或DROP语句，而数据库事件包括数据库服务器的启动/关闭事件，用户的登陆/断开事件，以及服务器错误。创建系统触发器的语法如下：view plaincopy to clipboardprint？CREATE [OR REPLACE] TRIGGER [schema.]trigger_name{BEFORE | AFTER}{ddl_event_list | database_event_list}ON {DATABASE | [schema.]SCHEMA}[when_clause]Trigger_body;CREATE [OR REPLACE] TRIGGER [schema.]trigger_name{BEFORE | AFTER}{ddl_event_list | database_event_list}ON {DATABASE | [schema.]SCHEMA}[when_clause]Trigger_body;其中，ddl_event_list是由OR关键字隔开的一个或多个DDL事件，database_event_list则是由OR关键字隔开的一个或多个数据库事件。注意：不能创建INSTEAD-OF系统级???发器。通过子句ON {DATABASE | [schema.]SCHEMA}我们可以指定这个系统触发器是定义在数据库级上还是模式级上。只要发生了激活事件，数据库级触发器就会激活。而只有激活事件发生在某个具体模式中，相应的模式级触发器才会激活。如果使用SCHEMA关键字的时候没有定义某个具体模式的名称，那么默认设置为拥有这个触发器的模式。9、修改触发器状态和删除触发器启动或禁用触发器ALTER TRIGGER trigger_name {DISABLE | ENABLE};删除触发器DROP TRIGGER trigger_name;还可以使用ALTER TABLE命令，并附加使用ENABLE ALL TRIGGERS或DISABLE ALL TRIGGERS子句，同时将某一个表上的所有触发器开启或关闭。ALTER TABLE table_name {ENABLE | DISABLE} ALL TRIGGERS;可以通过user_triggers来查看相应触发器信息。10、变化表和限制表触发器主体（trigger body）可以访问的表和列上有一些限制。在定义这些限制以前，我们先看两个概念—变化表和限制表。“变化表”（mutating table）是被DML语句正在修改的表。对于触发器而言，它就是定义触发器的表。需要作为DELETE CASCADE参考完整性限制（referential integrity constraints）的结果进行更新的表也是变化的（mutating）。“限制表”（constraining table）是可能需要对参考完整性限制执行读操作的表。为了更好的理解定义，我们看下面这个例子view plaincopy to clipboardprint？CREATE TABLE registered_students （student_id NUMBER（5） NOT NULL,department CHAR（3） NOT NULL,course NUMBER（3） NOT NULL,grade CHAR（1）,CONSTRAINT rs_gradeCHECK （grade IN （"A", "B", "C", "D", "E"））,CONSTRAINT rs_student_idFOREIGN KEY （student_id） REFERENCES students （id）,CONSTRAINT rs_department_courseFOREIGN KEY （department, course）REFERENCES classes （department, course））;--...CREATE TABLE registered_students （student_id NUMBER（5） NOT NULL,department CHAR（3） NOT NULL,course NUMBER（3） NOT NULL,grade CHAR（1）,CONSTRAINT rs_gradeCHECK （grade IN （"A", "B", "C", "D", "E"））,CONSTRAINT rs_student_idFOREIGN KEY （student_id） REFERENCES students （id）,CONSTRAINT rs_department_courseFOREIGN KEY （department, course）REFERENCES classes （department, course））;--...registered_students有两个声明的参考完整性限制。Students和classes都是registered_students的限制表。触发器主体中的SQL不允许进行：读取或修改触发语句（triggering statement）的任何变化表。这些表包括触发表（triggering table ）自己。读取或修改触发表（triggering table）的限制表中的主键（primary）、唯一列值（unique）或外键（foreign key）列。但是如果需要的话，可以修改其他列。

Oracle索引聚簇表的数据加载MySQL中插入数据列到特定列相关资讯 oracle

[INS-32052] Oracle基目录和Oracle （07/22/2014 07:41:41）
Oracle 4个大对象（lobs）数据类型（02/03/2013 12:33:05）
Oracle按时间段分组统计（07/26/2012 10:36:48）

[Oracle] dbms_metadata.get_ddl的（07/12/2013 07:37:30）
Liferay Portal 配置使用Oracle和（07/31/2012 20:07:18）
Concurrent Request:Inactive （07/20/2012 07:44:05）

本文评论查看全部评论（1）

评论声明

尊重网上道德，遵守中华人民共和国的各项有关法律法规