关于触发器的问题，reference old ,new之类的写法

[karlpan01]

CREATE OR REPLACE TRIGGER TRIG_FA_CARD
AFTER INSERT OR UPDATE OR DELETE ON FA_CARD
REFERENCING OLD AS old_row NEW AS new_row
FOR EACH ROW
DECLARE
v_action FA_CARD_EXP.ACTION%TYPE;
BEGIN

if inserting then
v_action := 'INSERT';
elsif updating then
v_action := 'UPDATE';
elsif deleting then
v_action := 'DELETE';
end if;

if v_action in ('INSERT', 'UPDATE') then

/**
insert into FA_CARD_EXP
(select FA_CARD.*,sysdate,v_action
from FA_CARD
where CARD_ID = :old_row.CARD_ID);
**/

insert into FA_CARD_EXP
(CARD_ID,
LATEST_MODIFY_DATE,
ACTION
)
values
(:new_row.CARD_ID,
sysdate,
v_action);

elsif v_action = 'DELETE' then

end if;
END;


我想知道为什么打开 /**
insert into FA_CARD_EXP
(select FA_CARD.*,sysdate,v_action
from FA_CARD
where CARD_ID = :old_row.CARD_ID);
**/
的注释，同时注释掉如下内容
insert into FA_CARD_EXP
(CARD_ID,
LATEST_MODIFY_DATE,
ACTION
)
values
(:new_row.CARD_ID,
sysdate,
v_action);
的时候，
然后执行update fa_card t set t.fa_name='&&&' where t.card_id='003103000000000001';
语句的时候触发触发器的时候
会报如下错误：
ORA-04091: 表 CZ52.FA_CARD 发生了变化，触发器/函数不能读
ORA-06512: 在"CZ52.TRIG_FA_CARD", line 16
ORA-04088: 触发器 'CZ52.TRIG_FA_CARD' 执行过程中出错

当然FA_CARD_EXP与FA_CARD相比仅多了LATEST_MODIFY_DATE,ACTION两个字段，且字段数序一致
如果不在触发器内，执行类似sql:
insert into FA_CARD_EXP
(select FA_CARD.*,sysdate,'321'
from FA_CARD
where CARD_ID = '123');
是可以正常运行的！

关于触发器不是非常了解，往大家多多帮助，我感觉两个语句没有太多的不同！

[oracledbalgtu]

原因是行级触发器(FOR EACH ROW),触发表不能对自己进行dml操作。
也就是FA_CARD表的行级触发器不能对FA_CARD进行dml操作。
表级触发器可以,没有这个限制。

[Quote=引用楼主 karlpan01 的帖子:]
CREATE OR REPLACE TRIGGER TRIG_FA_CARD
AFTER INSERT OR UPDATE OR DELETE ON FA_CARD
REFERENCING OLD AS old_row NEW AS new_row
FOR EACH ROW
DECLARE
v_action FA_CARD_EXP.ACTION%TYPE;
BEGIN

if inserting then
v_action := 'INSERT';
elsif updating then
v_action := 'UPDATE';
elsif deleting then
v_action := 'DELETE';
end if;

if v_action in ('I…
[/Quote]

[fxianxian]

你这个是行级触发器,顾名思义,就是表中的每一行记录发生变化的时候,都会触发这个触发器
REFERENCING OLD AS old_row NEW AS new_row
FOR EACH ROW
这个意思是OLD为变化之前的值,NEW为变化之后的值

[karlpan01]

答案已知！ths!
就这么一句话没有注意！