oracle触发器如何使用

oracle触发器如何使用

　　触发器 是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。触发器是许多关系数据库系统都提供的一项技术。在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 [OR REPLACE] TIGGER触发器名 触发时间 触发事件

　　ON表名/视图名

　　[FOR EACH ROW] //加上FOR EACH ROW 即为行级触发器，不加时为语句级触发器BEGINpl/sql语句

　　END

　　create [or replace] trigger [schema.]trigger_name

　　{before | after | instead of}

　　{delete [or insert][or update [of column,...n]]}

　　on [schema.]table_name | view_name

　　[for each row [when(condition)]]

　　sql_statement[,...n]

　　例如：

　　CREATE OR REPLACE TRIGGER trigger_name

　　< before | after | instead of > < insert | update | delete> ON table_name

　　[FOR EACH ROW]WHEN (condition)

　　DECLAREBEGINEND;

　　3)instead of 触发器语法

　　语法：

　　CREATE [OR REPLACE] TRIGGER trigger_name

　　INSTEAD OF{INSERT|DELETE|UPDATE [OF COLUMN...]}[OR {INSERT| DELETE| UPDATE [OF COLUMN...]}]ON VIEW_NAME[REFFERENCING{OLD [AS] OLD | NEW [AS] NEW| PARENT AS PARENT}] // 可以指定相关名称，当前的默认相关名称为OLD和NEW,

　　应用相关名称时需要加：[FOR EACH ROW] //instead of 触发器只能在行级上触发，因为没有必要指定[WHEN CONDITION]DECLARE

　　BEGINEND;

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

　　3.实例

　　创建测试表格：

　　CREATE TABLE "HNZC"."TRIGGERTEST"

　　(

　　"ID" VARCHAR2(20 BYTE),

　　"NAME" VARCHAR2(20 BYTE),

　　"SCORE" NUMBER

　　);create table tab1 select * from triggertest;

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

　　触发器如下：

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　AFTER INSERT ON TRIGGERTEST //插入后触发FOR EACH ROW //行级触发器BEGIN

　　INSERT INTO tab1(ID,NAME) VALUES('22','33');END;

　　执行语句：

　　insert into triggertest (id) values ('aabbcc');

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

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

　　触发器如下：

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　AFTER INSERT ON TRIGGERTEST

　　FOR EACH ROW

　　BEGIN

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

　　执行语句：

　　insert into triggertest (id) values ('aabbcc');

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

　　在行 1 上开始执行命令时出错:insert into triggertest (id) values ('aabbcc')

　　错误报告:

　　SQL 错误: ORA-20001: 不是上班时间，不能修改表格triggertest

　　ORA-06512: 在 "HNZC.TRIGGER1", line 3ORA-04088: 触发器 'HNZC.TRIGGER1' 执行过程中出错

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

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

　　FOR EACH ROW

　　BEGIN

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

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

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

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

　　FOR EACH ROW

　　WHEN(OLD.NAME='月儿')BEGIN

　　CASE WHEN UPDATING('SCORE') THEN

　　IF:NEW.SCORE<:OLD.SCORE THEN RAISE_APPLICATION_ERROR(-20001,'月儿的分数只能提升不能下降'); END IF; END CASE;END;

　　当前月儿的分数为20

　　当修改为10时出错

　　UPDATE "HNZC"."TRIGGERTEST" SET SCORE = '10' WHERE ROWID = 'AAAdEzAAPAAAAH+AAB' AND ORA_ROWSCN = '47685303'ORA-20001: 月儿的分数只能提升不能下降

　　ORA-06512: 在 "HNZC.TRIGGER1", line 4ORA-04088: 触发器 'HNZC.TRIGGER1' 执行过程中出错

　　当修改为30时成功

　　UPDATE "HNZC"."TRIGGERTEST" SET SCORE = '30' WHERE ROWID = 'AAAdEzAAPAAAAH+AAB' AND ORA_ROWSCN = '47685303'提交成功

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

　　触发器为：

　　CREATE OR REPLACE TRIGGER TRIGGER1

　　BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

　　FOR EACH ROW

　　BEGIN

　　TESTPRO1();END;

　　存储过程为：

　　create or replacePROCEDURE TESTPRO1 AS BEGIN

　　insert into tab1(id,name,score) VALUES('AAA','BBB',200);END TESTPRO1;

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

　　5)级联更新

　　触发器如下(triggertest表中name修改时同时修改tab1中的name)

　　create or replacePROCEDURE TESTPRO1 AS BEGIN

　　insert into tab1(id,name,score) VALUES('AAA','BBB',200);END TESTPRO1;

　　执行语句：

　　update triggertest set name= '水儿' where name='月儿';

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

　　6)instead of触发器

　　TABLE STUDENT表格数据如下

　　

　　创建视图student_view

　　CREATE OR REPLACE VIEW STUDNET_VIEW

　　AS SELECT CLASSID,AVG(SCORE) AVERAGE_SCORE FROM STUDENTGROUP BY CLASSID;

　　视图数据如下：

　　

　　对视图student_view 执行如下操作：

　　DELETE FROM STUDNET_VIEW WHERE CLASSID='111';

　　执行结果：

　　错误报告:

　　SQL 错误: ORA-01732: 此视图的数据操纵操作非法01732. 00000 - "data manipulation operation not legal on this view"

　　解决方法：创建INSTEAD OF 视图

　　CREATE OR REPLACE TRIGGER STUDENT_VIEW_DELETE

　　INSTEAD OF DELETE ON STUDNET_VIEW

　　FOR EACH ROWBEGIN

　　DELETE FROM STUDENT WHERE CLASSID=:OLD.CLASSID;END STUDENT_VIEW_DELETE;

　　执行删除语句

　　DELETE FROM STUDNET_VIEW WHERE CLASSID='111';

　　执行结果：删除成功

　　1 行已删除。

　　4.注意事项

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

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

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

　　4) 触发器最大为32K