_魔豆IT网

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。

1.说明

1）触发器是一种特殊的存储过程，触发器一般由事件触发并且不能接受参数，存储器由语句块去调用

2）触发器分类：

　　1.DML触发器：创建在表上，由DML事件引发

　　2.instead of触发器：创建在视图上并且只能在行级上触发，用于替代insert，delete等操作（由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，它是专门为进行视图操作的一种处理方法）

　　3.DDL触发器：触发事件时数据库对象的创建和修改

　　4.数据库事件触发器：定义在数据库或者模式上，由数据库事件触发

3）组成：

　　1.触发事件：引发触发器被触发的事件DML语句(INSERT,UPDATE,DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件

　　　　　　（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

　　2.触发时间：即该触发器是在触发事件发生之前（BEFORE）还是之后(AFTER)触发

　　3.触发操作：触发器触发后要完成的事情

　　4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，触发器才会执行触发操作。

　　5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发操作。

　　6.触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。（比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次）　　　　　　　

2.语法

1）说明

不同类型的触发器例如DML触发器，Instead of触发器，系统触发器语法格式区别较大

2)一般语法

　　CREATE[ORREPLACE]TIGGER触发器名触发时间触发事件　　ON表名/视图名　　[FOREACHROW]//加上FOREACHROW即为行级触发器，不加时为语句级触发器　　BEGIN　　pl/sql语句　　END

create[orreplace]trigger[schema.]trigger_name{before|after|insteadof}{delete[orinsert][orupdate[ofcolumn,...n]]}on[schema.]table_name|view_name[foreachrow[when(condition)]]sql_statement[,...n]

例如：

　　CREATEORREPLACETRIGGERtrigger_name　　<before|after|insteadof><insert|update|delete>ONtable_name　　[FOREACHROW]　　WHEN(condition)　　DECLARE　　BEGIN　　END;

3）instead of 触发器语法

语法：

CREATE[ORREPLACE]TRIGGERtrigger_nameINSTEADOF{INSERT|DELETE|UPDATE[OFCOLUMN...]}[OR{INSERT|DELETE|UPDATE[OFCOLUMN...]}]ONVIEW_NAME[REFFERENCING{OLD[AS]OLD|NEW[AS]NEW|PARENTASPARENT}]//可以指定相关名称，当前的默认相关名称为OLD和NEW,应用相关名称时需要加：[FOREACHROW]//insteadof触发器只能在行级上触发，因为没有必要指定[WHENCONDITION]DECLAREBEGINEND;

说明：INSTEAD OF 用于对视图的DML触发，由于视图可能有多个表进行联结而成，因而并非所有的联结均可更新，运用 INSTEAD OF 触发器可完成相应的操作。

3.实例

创建测试表格：

CREATETABLE"HNZC"."TRIGGERTEST"("ID"VARCHAR2(20BYTE),"NAME"VARCHAR2(20BYTE),"SCORE"NUMBER);createtabletab1select*fromtriggertest;

1)DML触发器/行级触发器

触发器如下：

CREATEORREPLACETRIGGERTRIGGER1AFTERINSERTONTRIGGERTEST//插入后触发FOREACHROW//行级触发器BEGININSERTINTOtab1(ID,NAME)VALUES('22','33');END;

执行语句：

insertintotriggertest(id)values('aabbcc');

语句执行结束，表tab1中新增加一条数据

2）限制对表的修改（例如非工作时间不能修改某些表）

触发器如下：

CREATEORREPLACETRIGGERTRIGGER1AFTERINSERTONTRIGGERTESTFOREACHROWBEGINIF(TO_CHAR(SYSDATE,'DAY')IN('星期三','星期天'))THENRAISE_APPLICATION_ERROR(-20001,'不是上班时间，不能修改表格triggertest');ENDIF;END;

执行语句：

insertintotriggertest(id)values('aabbcc');

今天周三因而输出结果为：

在行1上开始执行命令时出错:insertintotriggertest(id)values('aabbcc')错误报告:SQL错误:ORA-20001:不是上班时间，不能修改表格triggertestORA-06512:在"HNZC.TRIGGER1",line3ORA-04088:触发器'HNZC.TRIGGER1'执行过程中出错

通常对表的修改限制如下（即周一至周五9——18点能修改表格）

CREATEORREPLACETRIGGERTRIGGER1BEFOREINSERTORDELETEORUPDATEONTRIGGERTESTFOREACHROWBEGINIF(TO_CHAR(SYSDATE,'DAY')IN('星期六','星期天'))OR(TO_CHAR(SYSDATE,'HH24:MI')NOTBETWEEN'9:00'AND'18:00')THENRAISE_APPLICATION_ERROR(-20001,'不是上班时间，不能修改表格triggertest');ENDIF;END;

3）增加限制条件（如不能更改某个员工的记录）

触发器如下：（如下实现月儿的分数只能增加）

CREATEORREPLACETRIGGERTRIGGER1BEFOREINSERTORDELETEORUPDATEONTRIGGERTESTFOREACHROWWHEN(OLD.NAME='月儿')BEGINCASEWHENUPDATING('SCORE')THENIF:NEW.SCORE<:OLD.SCORETHENRAISE_APPLICATION_ERROR(-20001,'月儿的分数只能提升不能下降');ENDIF;ENDCASE;END;

当前月儿的分数为20

当修改为10时出错

UPDATE"HNZC"."TRIGGERTEST"SETSCORE='10'WHEREROWID='AAAdEzAAPAAAAH+AAB'ANDORA_ROWSCN='47685303'ORA-20001:月儿的分数只能提升不能下降ORA-06512:在"HNZC.TRIGGER1",line4ORA-04088:触发器'HNZC.TRIGGER1'执行过程中出错

当修改为30时成功

UPDATE"HNZC"."TRIGGERTEST"SETSCORE='30'WHEREROWID='AAAdEzAAPAAAAH+AAB'ANDORA_ROWSCN='47685303'提交成功

4）在触发器中调用存储过程

触发器为：

CREATEORREPLACETRIGGERTRIGGER1BEFOREINSERTORDELETEORUPDATEONTRIGGERTESTFOREACHROWBEGINTESTPRO1();END;

存储过程为：

createorreplacePROCEDURETESTPRO1ASBEGINinsertintotab1(id,name,score)VALUES('AAA','BBB',200);ENDTESTPRO1;

执行完毕后tab1中增加一条数据

5）级联更新

触发器如下（triggertest表中name修改时同时修改tab1中的name）

createorreplacePROCEDURETESTPRO1ASBEGINinsertintotab1(id,name,score)VALUES('AAA','BBB',200);ENDTESTPRO1;

执行语句：

updatetriggertestsetname='水儿'wherename='月儿';

结果：tab1中name为月儿的也更改为水儿

6）instead of触发器

TABLE STUDENT表格数据如下

创建视图student_view

CREATEORREPLACEVIEWSTUDNET_VIEWASSELECTCLASSID,AVG(SCORE)AVERAGE_SCOREFROMSTUDENTGROUPBYCLASSID;

视图数据如下：

对视图student_view 执行如下操作：

DELETEFROMSTUDNET_VIEWWHERECLASSID='111';

执行结果：

错误报告:SQL错误:ORA-01732:此视图的数据操纵操作非法01732.00000-"datamanipulationoperationnotlegalonthisview"

解决方法：创建INSTEAD OF 视图

CREATEORREPLACETRIGGERSTUDENT_VIEW_DELETEINSTEADOFDELETEONSTUDNET_VIEWFOREACHROWBEGINDELETEFROMSTUDENTWHERECLASSID=:OLD.CLASSID;ENDSTUDENT_VIEW_DELETE;

执行删除语句

DELETEFROMSTUDNET_VIEWWHERECLASSID='111';

执行结果：删除成功

1行已删除。

4.注意事项

1）在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）

2）触发器中不能使用commit语句，触发器的操作与触发事件（INSERT,UPDATE,DELETE）一起进行COMMIT和ROLLBACK;

3) 一个表上的触发器越多，对于表的DML操作性能影响越大

4）触发器最大为32K

近期关注

相关阅读

热门阅读