create or replace trigger TR_TEST after delete on t1 for each row declare v_num1 varchar2(20); begin set v_num1 := :old.col1; update t2 set 对应字段名 := 值 where col1=v_num1; end;
create or replace trigger tg_t1 after delete on t1 for each row begin update t2 set col2=:old.col2,col3=:old.col3 where col1=:old.col1; end tg_t1;
我是这样写的 CREATE OR REPLACE TRIGGER TG_UNLOADQ BEFORE DELETE ON T_LOG_QDEPTH FOR EACH ROW DECLARE CURRENT_QDEPTH INT; MAX_QDEPTH INT; VAR_QUEUENAME VARCHAR2(50); BEGIN VAR_QUEUENAME := :OLD.QUEUENAME; SELECT MAXDEPTH INTO MAX_QDEPTH FROM T_CONF_QUEUEINFO WHERE NAME=VAR_QUEUENAME; SELECT COUNT(*) INTO CURRENT_QDEPTH FROM T_LOG_QDEPTH WHERE QUEUENAME=VAR_QUEUENAME; IF CURRENT_QDEPTH=MAX_QDEPTH THEN UPDATE T_CONF_QUEUEINFO SET LENGTHFLAG=0 WHERE NAME=VAR_QUEUENAME; END IF;
END TG_UNLOADQ;但是总报这个错误 ORA-04091: table T_LOG_QDEPTH is mutating, trigger/function may not see it ORA-06512: at "TG_UNLOADQ", line 8 ORA-04088: error during execution of trigger 'TG_UNLOADQ' 用的before 按理不该报这个错啊 各位高人给指点一下
SELECT COUNT(*) INTO CURRENT_QDEPTH FROM T_LOG_QDEPTH WHERE QUEUENAME=VAR_QUEUENAME; 这一句不行,行级触发器中不能引用触发的表本身 这么改如何 CREATE OR REPLACE TRIGGER TG_UNLOADQ BEFORE DELETE ON T_LOG_QDEPTH FOR EACH ROW DECLARE VAR_QUEUENAME VARCHAR2(50); BEGIN VAR_QUEUENAME := :OLD.QUEUENAME; UPDATE T_CONF_QUEUEINFO SET LENGTHFLAG=0 WHERE NAME=VAR_QUEUENAME and lengthflag<>0; end tg_unloadq;
或者这么写 CREATE OR REPLACE TRIGGER TG_UNLOADQ BEFORE DELETE ON T_LOG_QDEPTH FOR EACH ROW DECLARE VAR_LENGTHFLAG number; BEGIN select LENGTHFLAG into VAR_LENGTHFLAG from T_CONF_QUEUEINFO where NAME=:OLD.QUEUENAME; if VAR_LENGTHFLAG<>0 then UPDATE T_CONF_QUEUEINFO SET LENGTHFLAG=0 WHERE NAME=:OLD.QUEUENAME; end if; end tg_unloadq; 不过要确定下,T_CONF的MAXDEPTH 代表T_LOG_QDEPTH表的相应记录删除前的总条数对吗
after delete
on t1
for each row
declare v_num1 varchar2(20);
begin
set v_num1 := :old.col1;
update t2 set 对应字段名 := 值 where col1=v_num1;
end;
after delete on t1
for each row
begin
update t2 set col2=:old.col2,col3=:old.col3 where col1=:old.col1;
end tg_t1;
CREATE OR REPLACE TRIGGER TG_UNLOADQ
BEFORE DELETE ON T_LOG_QDEPTH
FOR EACH ROW
DECLARE
CURRENT_QDEPTH INT;
MAX_QDEPTH INT;
VAR_QUEUENAME VARCHAR2(50);
BEGIN
VAR_QUEUENAME := :OLD.QUEUENAME;
SELECT MAXDEPTH INTO MAX_QDEPTH FROM T_CONF_QUEUEINFO WHERE NAME=VAR_QUEUENAME;
SELECT COUNT(*) INTO CURRENT_QDEPTH FROM T_LOG_QDEPTH WHERE QUEUENAME=VAR_QUEUENAME;
IF CURRENT_QDEPTH=MAX_QDEPTH THEN
UPDATE T_CONF_QUEUEINFO SET LENGTHFLAG=0 WHERE NAME=VAR_QUEUENAME;
END IF;
END TG_UNLOADQ;但是总报这个错误
ORA-04091: table T_LOG_QDEPTH is mutating, trigger/function may not see it ORA-06512: at "TG_UNLOADQ", line 8 ORA-04088: error during execution of trigger 'TG_UNLOADQ'
用的before 按理不该报这个错啊 各位高人给指点一下
这一句不行,行级触发器中不能引用触发的表本身
这么改如何
CREATE OR REPLACE TRIGGER TG_UNLOADQ
BEFORE DELETE ON T_LOG_QDEPTH
FOR EACH ROW
DECLARE
VAR_QUEUENAME VARCHAR2(50);
BEGIN
VAR_QUEUENAME := :OLD.QUEUENAME;
UPDATE T_CONF_QUEUEINFO SET LENGTHFLAG=0 WHERE NAME=VAR_QUEUENAME and lengthflag<>0;
end tg_unloadq;
但是业务上要求必须根据此时T_LOG_QDEPTH表里记录的个数来判断是否要对T_CONF_QUEUEINFO表中相应记录作修改
因为T_LOG_QDEPTH表中一个QUEUENAME对应数条记录,而T_CONF的MAXDEPTH 代表T_LOG_QDEPTH表的相应记录的条数吧。查询记录的个数是为了不会重复更新LENGTHFLAG=0对吧?
那么在条件里加个lengthflag<>0也是一样的
CREATE OR REPLACE TRIGGER TG_UNLOADQ
BEFORE DELETE ON T_LOG_QDEPTH
FOR EACH ROW
DECLARE
VAR_LENGTHFLAG number;
BEGIN
select LENGTHFLAG into VAR_LENGTHFLAG from T_CONF_QUEUEINFO where NAME=:OLD.QUEUENAME;
if VAR_LENGTHFLAG<>0 then
UPDATE T_CONF_QUEUEINFO SET LENGTHFLAG=0 WHERE NAME=:OLD.QUEUENAME;
end if;
end tg_unloadq;
不过要确定下,T_CONF的MAXDEPTH 代表T_LOG_QDEPTH表的相应记录删除前的总条数对吗
当T_LOG_QDEPTH表里 某个QUEUENAME的记录个数达到T_CONF表里MAXDEPTH的值时 就更新LENGTHFLAG