LOB.Write and after update trigger

[nicofari]

Hi all,

I am writing an "audit" trigger to write any changes on a table containing a clob to an audit table.
The base table is edited by an TOracleDataset which
uses the sequence:
update table set clob_field=empty_clob()... returning .. and LOB.write
The problem is that
in this way the trigger is fired with empty_clob and
doesnt get the new value.

I need an advice about which is the best approach.
I don't like very much put the trigger logic in the client application but it seems the only way, am I wrong?

 

[Marco Kalter]

If you are using Oracle Net 8.1 or later, you can use a Temporary LOB instead, and write the LOB data before performing the update. Temporary LOB's are new in Direct Oracle Access 4.0.

 

[nicofari]

Yes, but the application I want to audit uses a standard DOA TOracleDataset in edit state (so clob update is entirely handled by the toracledataset).
I would need to change the source code of TOracleDataset (regarding the treatment of clob fields) but it seems not to me a very good idea..
am I wrong?

 

[al0]

Originally posted by nicofari:
Yes, but the application I want to audit uses a standard DOA TOracleDataset in edit state (so clob update is entirely handled by the toracledataset).
I would need to change the source code of TOracleDataset (regarding the treatment of clob fields) but it seems not to me a very good idea..
am I wrong?

Definitely - you need not to change source code, it just enough to use TApplyRecordEvent of TOracleDataset. But there is much worse problem related not to DOA but to Oracle itself. SQL refernce stated
Note: Using OCI functions or the DBMS_LOB package to update
LOB values or LOB attributes of object columns does not cause
Oracle to fire triggers defined on the table containing the columns
or the attributes.

So you have a fair chance that your trigger will not be fired at all, so you lost a main advantage of using trigger instead of auditing in application code.

 

[Marco Kalter]

If you are using 4.0 then the TOracleDataSet will use temporary LOB's if TOracleSession.Preferences.TemporaryLOB is set to tlCache or tlNoCache:

property TemporaryLOB: TTemporaryLOBOption;

type TTemporaryLOBOption = (tlNone, tlCache, tlNoCache);

Determines if a BLOB or CLOB posted through a TOracleDataSet should be passed as a normal LOB (tlNone), a cached temporary LOB (tlCache), or an uncached temporary LOB (tlNoCache).For more information about temporary LOB's, see the TLOBLocator.CreateTemporary section. This property is ignored on Oracle Net 8.0 clients, which do not support temprary LOB's.

 

[nicofari]

I missed this! Thank you very much!!

 

[al0]

Originally posted by Marco Kalter:
If you are using 4.0 then the TOracleDataSet will use temporary LOB's if TOracleSession.Preferences.TemporaryLOB is set to tlCache or tlNoCache: ...

May I kindly ask to make documentation more clear at this point? No it is so abbreviated that it is really hard to catch what this property means.