问题描述
我试图寻找答案，但是找不到任何有用的东西.要么没有，要么我的搜索功能崩溃了.无论如何，这是我的情况:
I tried searching for answers to this but couldn't really find anything useful. Either there isn't or my searching capabilities took a knock. Regardless, here's my situation:
我有一个例子，我有两个相同的表Z_TEST和Z_TEST2.如果需要，可将Z_TEST2称为审核表.
I have a case where I've got 2 identical tables, Z_TEST and Z_TEST2. Call Z_TEST2 an audit table if you'd like.
都有两列，ID(数字)和CFIELD(CLOB). Z_TEST有一个触发器，该触发器将插入或更新Z_TEST2(仅对CLOB字段有效).
Both have 2 columns, ID (number) and CFIELD (CLOB). Z_TEST has a trigger which will either insert or update Z_TEST2 (in effect only the CLOB field).
方案1:
如果我在Z_TEST上创建以下触发器(这是正常触发器)，则审计表也将更新:
If I create the following trigger on Z_TEST, which is a normal trigger, the audit table gets updated too:
CREATE OR REPLACE TRIGGER Z_TEST_TRIG
AFTER INSERT OR UPDATE ON Z_TEST
FOR EACH ROW
Begin
If inserting then
Begin
insert into Z_TEST2 values(:new.ID, :new.CFIELD);
end;
end if;
if updating then
begin
update Z_TEST2 Set CFIELD = :new.CFIELD where ID = :new.id;
end;
end if;
End;
方案2:
如果我创建以下触发器，它是一个复合触发器，并利用其每行之后"块，则审核表的CLOB将更新为空，即为空:
If I create the following trigger which is a compound trigger and make use of its "After each row" block, the audit table's CLOB updates with nothing, null:
create or replace trigger Z_TEST_TRIG
FOR UPDATE OR INSERT ON Z_TEST
COMPOUND TRIGGER
AFTER EACH ROW IS
Begin
If inserting then
Begin
insert into Z_TEST2 values(:new.ID, :new.CFIELD);
end;
end if;
if updating then
begin
update Z_TEST2 Set CFIELD = :new.CFIELD where ID = :new.id;
end;
end if;
END AFTER EACH ROW;
END Z_TEST_TRIG;
有人知道为什么这对方案1有效但对方案2无效吗?我们的WHOLE框架基于场景2(复合触发器)，而我直到最近才遇到使用CLOB数据类型的需求，随之而来的是这个问题.
Does anyone have any idea why this would work for scenario 1, but not 2? Our WHOLE framework is based on scenario 2 (compound triggers) and I've only recently come across the need to use CLOB data types, and with it came this problem.
为什么会有区别，我的代码缺少什么?
Why the difference and is my code missing something?
推荐答案
由于没有时间来解决这个问题，我通过使用变量解决了这个问题.
Having no time to linger on this issue I solved it by making use of a variable.
在声明部分声明一个CLOB变量，然后在BROWORE EACH ROW或AFTER EACH ROW中为它分配:new.clob_field的值，并在您的insert/update语句而不是:new.clob_field中使用该变量在触发器内解决了这个问题.
Declaring a CLOB variable in the declaration section and assigning the value of the :new.clob_field to it either in BEFORE EACH ROW, or AFTER EACH ROW, and using the variable in your insert/update statement rather than :new.clob_field within the trigger solves this issue.
我遇到了很多与之作斗争的帖子(特别是复合触发器，而不是简单的触发器)，所以我希望我在此上花费的时间可以帮助其他人并节省他们的时间.
I came across a lot of posts by people battling with this (compound triggers specifically, not simple triggers), so I hope the time i spent on this helps someone else and saves them time.
如果任何人遇到这篇帖子，并且知道:new.clob_field在每行节之前/之后的插入/更新语句中使用时，在复合触发器中失去其值的原因，这对我的理智确实有帮助.死于这一念头的日子真是太可怕了...
It would really be helpful to my sanity if anyone comes across this post who knows the reason why :new.clob_field loses its value in a compound trigger when used in insert/update statements in the BEFORE/AFTER each row section. It would be awful dying one day with this thought stuck in my mind...
我还将假设它也适用于BLOB(如果这会引起问题).
I'll also make the assumption that this would work for BLOB as well (if that causes an issue).
这篇关于Oracle(11g)复合触发器不更新CLOB数据字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！