How to use CLOB in a trigger

[ths]

Hello,
i have the following problem that may be relevant or not relevant on this forum.
Suppose we have a table with two CLOB columns and a trigger like:

create table clobtable (id number, clob1 clob, clob2 clob);
CREATE OR REPLACE TRIGGER B_IN_UP_CLOBTABLE
BEFORE UPDATE OR INSERT ON CLOBTABLE FOR EACH ROW
BEGIN
:NEW.CLOB2 := :NEW.CLOB1;
END;

If you run Insert and/or Update statements on this table using some tool (Toad):

INSERT INTO CLOBTABLE (id, clob1, clob2) values (1, 'aaaa','bbbb');
update clobtable set clob1 = 'cccc' where id = 1;

you will obtain expected result - both CLOB fields will contain 'aaaa' after Insert and also both fields will be 'cccc' after Update.
However, using ODAC Query Component i get a problem. Automatically generated by the Component Update statement is
UPDATE CLOBTABLE
SET
CLOB1=EMPTY_CLOB()
WHERE
ID = :Old_ID
RETURNING
CLOB1
INTO
:CLOB1
and it works fine for itself. The problem is that the trigger does not work now - the field CLOB2 is empty after this update.
How should i change the code of the trigger to obtain correct result working with a client application that uses DevArt Query Component?

Thanks,
Vladimir

[AlexP]

Hello,

In order for your trigger to handle when working in OraQuery, you should set the OraQuery.Options.TemporaryLobUpdate property to True.

[ths]

Hello Alex,
thank you very much. The test client works now. Could you explain please what are the advantages and disadvantages of setting this property to TRUE or FALSE? In dbMonitor i see that the Update Statement sent to the server is now (TemporaryLobUpdate=TRUE) "a standard" SQL:
UPDATE CLOBTABLE
SET
CLOB1 = :CLOB1
WHERE
ID = :Old_ID

[AlexP]

Yes, when using this option, DDL is changed, and we ourselves perform all the operations with LOB locators.