oracle触发器使用

首页 > 代码库 > oracle触发器使用

2024-10-27 15:53:39 209人阅读

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 即为行级触发器，不加时为语句级触发器
　　BEGIN
　　pl/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)
　　DECLARE
　　BEGIN
　　END;

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
BEGIN
END;

说明：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 3
　　ORA-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 4
　　ORA-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 replace
　　PROCEDURE TESTPRO1 AS
　　BEGIN
　　 insert into tab1(id,name,score) VALUES(‘AAA‘,‘BBB‘,200);
　　END TESTPRO1;
执行完毕后tab1中增加一条数据

5）级联更新
触发器如下（triggertest表中name修改时同时修改tab1中的name）
　　create or replace
　　PROCEDURE 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 STUDENT
　　GROUP 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 ROW
　　BEGIN
　　 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

oracle触发器使用

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > oracle触发器使用

oracle触发器使用

看完仍有疑问？有类似问题直接问程序猿