oracle触发器实例

2013-05-03

触发器（trigger）是个特殊的存储过程，由事件来触发，可对一个表进行（insert，delete， update）操作时激活它执行.对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

#############################################################################
1、触发器的语法如下

CREATE OR REPLACE TRIGGER trigger_name
ON table_name
[FOR EACH ROW]
WHEN (condition)
DECLARE
BEGIN
--触发器代码
END;
#############################################################################
2、删改查trigger
-- 禁止触发器
alter trigger tr_emp_salary disable;
-- 激活触发器
alter trigger tr_emp_salary enable;
-- 禁止表的所有触发器
alter table employee disable all triggers;
-- 激活表的所有触发器
alter table employee enable all triggers;
-- 重新编译触发器
--alter table tr_emp_salary compile;
-- 删除触发器
drop trigger table_name_COL_C2_tri_BU;
-- 查已有触发器
select TRIGGER_NAME from user_triggers;
set hea off
select * from user_triggers;
#############################################################################
3、Oracle触发器中的NEW与OLD

:OLD 与:NEW 的区别：

new是新插入的数据，old是原来的数据

insert只会有new，代表着要插入的新记录

delete只会有old，代表着要删除的记录

update由于执行的是先删除旧的记录，再插入新的记录，因此new和old都会有，且含义与上面的相同

#############################################################################

4、自治事物
在写trigger的时候,经常会遇到这种情况

当在程序块中需要对trigger本表进行修改或查询的时候,系统会提示错误: ORA-04091: table is mutating, trigger/function may not see it

关于这个错误,其实是由于对本表的操作造成的.ORACLE DB里默认在写TRIGGER的时候把本表锁死,不允许对其进行操作,也就是说这个错误是不能通过系统的手段解决的,只能改用一些其它的SQL来绕开它.

对此可通过自治事物解决：

SQL> CREATE OR REPLACE TRIGGER TR_T
2 AFTER DELETE ON T
3 FOR EACH ROW
4 DECLARE V_COUNT NUMBER;
5 PRAGMA AUTONOMOUS_TRANSACTION;--开启自治事物
6 BEGIN
7 INSERT INTO T VALUES(:OLD.ID,:OLD.MC,SYSDATE);
8 COMMIT;--提交自治事物
9 END TR_DEL_CABLE;
10 /

触发器已创建

#############################################################################
5、实例1
--table_name_COL_C2_tri_bu.sql
--select dbms_lob.getlength(COL_C2) from table_name where id=106;

CREATE or REPLACE TRIGGER table_name_COL_C2_tri_BU
before update ON table_name
FOR EACH ROW
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
if dbms_lob.getlength(:new.COL_C2) < 40 then raise_application_error(-20010,'value is small,cancel update.'); COMMIT; end if; end; / --用变量保存查询结果 --table_name_COL_C2_tri_bu.sql --select dbms_lob.getlength(COL_C2) from table_name where id=108; CREATE or REPLACE TRIGGER table_name_COL_C2_tri_BU before update ON table_name FOR EACH ROW declare sumC2 number(10); PRAGMA AUTONOMOUS_TRANSACTION; begin select sum(dbms_lob.getlength(:new.COL_C2)) into sumC2 from table_name where id=:new.id; if sumC2 < 40 then raise_application_error(-20010,'value is small,cancel update.'); COMMIT; end if; end; / ############################################################################# 6、实例2 update of salary on employee -- 指定该触发器为行级触发器 for each row -- 如果要定义变量，则只能在Declare中定义 declare maxSalary number(10,2); begin select max(salary) into maxSalary from employee; if :new.salary > maxSalary then
raise_application_error(-20010,'员工工资超出工资上限！');
end if;
end;
/

#############################################3
7、trigger保存成sql文件

@table_name_COL_C2_tri_bu.sql

Trigger created.
#############################################
8、获取BLOB列长度dbms_lob.getlength(COL_C2)

select sum(dbms_lob.getlength(COL_C2)) from table_name where id=108;
select sum(dbms_lob.getlength(COL_C2)) from table_name where id=106;
update table_name j set j.COL_C2=(select x.COL_C2 from table_name x where x.id='108') where j.id=106;

分类：数据库 | 标签： |

相关日志

评论被关闭！