有一个表a 当修改表a里某个字段为多少的时候,就往表b里插入这行被修改的数据 并且删除表a里被修改的那条数据这个触发器 我可以完成往表b里插入数据 但要是添加delete去删除表a的数据 这个触发器就不能用 这个该怎么办了?
解决方案 »
- 多表联查
- 关于游标与存储过程结合.实现多条件查询,帮我看下这个存储过程的方法无法使用
- oracle监听报错。SOS......
- 在调用oci中的OCIStmtExecute出现ORA-24324:service handle not initlize
- 600万条海量记录啊!!!!!!大家有高性能的分页存储过程帮我解决分页吗??要不我要在公司过年了!!!!哇哇。。。。
- 一个简单的T-SQL语句,怎么变成相对的PL/SQL语句?
- 一个sql语句
- 急啊!!使用imp 指令时遇到ORA-12560:TNS:协议适配器错误,怎么办?
- oracle中字段内容拆分方法
- 求助sql查询
- 【急】程序一次执行2句sql?
- oracle有没有像db2中Federated Database技术
2。在a表建一个行级触发器,对设计的数据保存到全局变量。
3。再建一个语句级触发器,进行delete操作。
步骤就是这样,你可以查一下变异表的解决办法
after update
on emp for each row
begin
if :new.sal=20000 then
insert into emp2(sal) values(:new.sal);
end if
delete from emp where sal=:new.sal;
end;
修改emp表,如果修改值为20000 那么就往emp2表中插入改修改的行,并且 删除掉emp表中被修改的行 但添加delete后 改触发器会出问题 请问该怎么改! 在线等 感谢
trigger抛出ORA-04091异常,无非是当前trigger下的事务access了一mutating table,比较常见的就是trigger访问了自身上的表.在一个指定on delete cascade模式下的父子表中,trigger中如果有对其相关的父/子表的访问,依然会抛出ORA-04091.这是比较隐性的.
拿oracle的示例表emp和dept来做这个试验.
dept的表结构如下:
create table DEPT
(DEPTNO NUMBER(2) not null primary key,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
emp表结构如下:create table EMP
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
foreign key(deptno) references dept(deptno) on delete cascade);
emp和dept是一对父子表,关联column为DEPTNO.
接下来创建1个 table:emp_log 和1个语句级 trigger: emp_del_trg.取一个
最简单的业务功能,emp_del_trg的作用就是当表emp记录被删除的时候,触发器将删除的记录的 EMPNO,DNAME和删除时间写入到emp_log中,当子表依赖的父表相关记录删除的时候,emp_log不做处理.
表emp_log的结构如下:
create table EMP_LOG
(ENAME VARCHAR2(20),
DNAME VARCHAR2(20),
DATES DATE );
触发器trigger代码如下:
create or replace trigger emp_del_trg after delete on emp for each row
begin
insert into emp_log
select :old.ename, dname, sysdate from dept where deptno = :old.deptno;
end;
来看看这个触发器是否能正常工作,首先删除emp的记录.
SQL> delete from emp where rownum<5;
4 rows deleted
SQL> select * from emp_log;
ENAME DNAME DATES
——————– ——————– ———–
SMITH RESEARCH 2008-7-1 18
ALLEN SALES 2008-7-1 18
WARD SALES 2008-7-1 18
JONES RESEARCH 2008-7-1 18
SQL> rollback;
Rollback complete
看似trigger工作正常,删除dept的记录呢?
SQL> delete from dept where rownum<3;
delete from dept
ORA-04091: 表 KEVINYUAN.DEPT 发生了变化, 触发器/函数不能读它
ORA-06512: 在 “KEVINYUAN.EMP_DEL_TRG”, line 2
ORA-04088: 触发器 ‘KEVINYUAN.EMP_DEL_TRG’ 执行过程中出错
ORA-04091错误如约而至.不难解释,因为父子表指定了级联删除,删除dept的记录
从而引起删除emp表上的相应数据,然后触发了emp_del_trg,由于trigger里有对dept的访问,对当前事务说,dept就是一个mutating table,这是不被允许的.
如何来解决这个问题而实现这个简单的业务逻辑功能呢?当然,从表结构逻辑设计上来讲,可以将dept表上的dname字段add到emp表,或者不要显式的指定references,用程序来维护数据的完整性和约束,然后调整业务代码.最直接的方法,在trigger中声明一个ora-04091的exception,对此异常不做处理,也可完成目的。
我们不妨做一下变通处理.将行级级触发器变通成语句级触发器.看下面的处理.
1.创建一个package:emp_pkg .
1. CREATE OR REPLACE PACKAGE emp_pkg AS
2. /* ----------------------------------
3. --Author:Kevin.yuan
4. --create_time: 2008 -07-01
5. ---------------------------------- */
6. TYPE crArray IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
7. oldRows crArray; --accept the old values of emp
8. oldEmpty crArray; --initialize values
9. END emp_pkg;
2.创建一个语句级trigger,用来触发trigger的时候清空初始化数据.
1. CREATE OR REPLACE TRIGGER emp_bd_st BEFORE DELETE ON emp
2. /*----------------------------------
3. Author:Kevin.yuan
4. create_time: 2008 -07-01
5. ---------------------------------- */
6. BEGIN
7. emp_pkg.oldRows := emp_pkg.oldEmpty;
8. END emp_bd_st;
3.创建一个行级trigger:emp_d,问题的核心和关键就在这里,这个trigger
并不参与业务逻辑,只是将触发到的数据载入到emp_pkg.oldRows记录表里面去.
1. CREATE OR REPLACE TRIGGER emp_d AFTER DELETE ON emp FOR EACH ROW
2. /* ----------------------------------
3. --Author:Kevin.yuan
4. --create_time: 2008 -07-01
5. ---------------------------------- */
6. DECLARE
7. --ct is the position of the deleted records
8. ct INTEGER := emp_pkg.oldRows.COUNT + 1;
9. BEGIN
10. emp_pkg.oldRows(ct).ename := :OLD.ename;
11. emp_pkg.oldRows(ct).deptno := :OLD.deptno;
12. END emp_d;
4.创建一个语句级trigger:emp_d_st,前面3步都是为这一步服务的,这一步
真正参与业务逻辑处理.
1. CREATE OR REPLACE TRIGGER emp_d_st AFTER DELETE ON emp
2. /* ----------------------------------
3. --Author:Kevin.yuan
4. --create_time: 2008 -07-01
5. ---------------------------------- */
6. DECLARE
7. BEGIN
8. FOR i IN 1 .. emp_pkg.oldRows.COUNT LOOP
9. INSERT INTO emp_log
10. (ename, dname, dates)
11. select emp_pkg.oldRows(i) .ename, dname, sysdate
12. from dept
13. WHERE deptno = emp_pkg.oldRows(i).deptno;
14. END LOOP;
15. END emp_d_st;
来看一下变通后的触发器是否满足我们的业务要求:
SQL> alter trigger emp_del_trg disable;
Trigger altered
删除emp数据
SQL> delete from emp where rownum<3;
2 rows deleted
SQL> select * from emp_log;
ENAME DNAME DATES
——————– ——————– ———–
SMITH RESEARCH 2008-7-1 19
ALLEN SALES 2008-7-1 19
SQL> rollback;
Rollback complete
删除dept数据.
SQL> delete from dept;
4 rows deleted
SQL> select * from emp_log;
ENAME DNAME DATES
——————– ——————– ———–
至此,目的实现.由于用了多于常规数量的触发器,对系统性能会造成一定影响,而且,无疑会加重系统后期业务维护负担,因此,良好的数据库逻辑设计和代码编写思路是很必要的,否则,只能走另外一些路径,不过,这个由行级触发器变语句级别触发器的思路,还是有必要的,当trigger中无法避免的需要access自身表的时候,这无疑是个可以借鉴的解决方案。
sorry1。建一个全局的嵌套表。
2。在a表建一个行级触发器,对设计的数据保存到全局变量。
3。再建一个语句级触发器,进行delete操作。