Inconsistent CLOB value in 'After Update' trigger...

[roymb]

I've setup an 'After Update' trigger on my table (project)
to record changes in a clob column called 'Note'.
My Project table structure is: (prj_id number(6), note clob).

My trigger definition is:

1. Create or Replace Trigger PROJECT_U
2. AFTER UPDATE on PROJECT
3. for EACH ROW
4. begin
-- dump structure: (oldv varchar2(500), newv varchar2(500)
5. insert into dump values(DBMS_LOB.substr(

ld.note,500,1),
6. DBMS_LOB.substrnew.note,500,1));
7. end;

Here's what's happening:

Good Behaviour: When I use TOracleQuery (tsql) and execute the following:

tsql.SQL.Clear;
tsql.SQL.Add('Update project set note=''You will see this...'' where prj_id=1001');
tsql.Execute;

the :new.note in the trigger body returns 'You will see this...'. This is OK!

Unacceptable Behaviour: When I use a TOracleDataset (tds), tie it with
a TwwDBGrid and edit the 'note' column in the grid (e.g. typed 'You
should see this...'), the trigger body fires but the :new.note returns
an empty string.

I set the TOracleDataset as follows:

tds.Active := False;
tds.SQL.Clear;
tds.SQL.Add('select p.*, p.rowid from project p'); // updateable table
tds.Active := True; // this will show the rows in the grid.

Interestingly, this also happened in the PL/SQL Developer when I edited the
'note' column directly in the grid. The plain SQL 'Update project set...'
behaved properly as above.

Can anybody please help? I'm

...

 

[Marco Kalter]

Both the TOracleDataSet and PL/SQL Developer will write the CLOB data through a TLOBLocator. First an empty_clob() is assigned to the column, and after the update, the CLOB data is written. In your TOracleQuery example you pass a string to the CLOB column.

You can set TOracleDataSet.Debug to True or you can use the Oracle Monitor to find out what exactly is being executed.

------------------
Marco Kalter
Allround Automations

 

[roymb]

I tried a Statement-level "after update" trigger, hoping to catch the "updated" value of the clob, but still I get the empty string (due to the assignment of empty_clob() in the update). Does anyone has any alternative solution for this?

 

[Marco Kalter]

The LOB data is written after the statement, and therefore after the "after statement" trigger. The only solution I see is that you perform the trigger logic from inside the application that performs the updates.

------------------
Marco Kalter
Allround Automations