Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Catching a CLOB "new value" inside trigger

Catching a CLOB "new value" inside trigger

From: RoyMB <RBalacano_at_sapplicada.com>
Date: Tue, 18 Dec 2001 18:14:07 GMT
Message-ID: <PHLT7.220$8i2.7574@news2.mts.net>

I'm developing a trigger-driven transaction logging system to record changes in columns of a table. One of my tables (PROJECT) has a CLOB column called NOTES. I understand that you can only access the :new CLOB value in 'After Update' or 'Instead Of' triggers. I'm using the 'After Update' trigger. Here's what's happening:

My trigger for PROJECT table looks like this:

CREATE or REPLACE TRIGGER Proj_Txlog
AFTER UPDATE on PROJECT
for EACH ROW
begin

If I update the CLOB value using the following PL/SQL lines, the trigger can catch and log the changes.

update PROJECT set NOTES = 'Any string up to 4000 characters...' where project_id=1006

When I'm updating the CLOB with more than 4000 characters, I can't use the line above. Instead, I use the following codes:

PROCEDURE update_Note(proj_id NUMBER) IS   newNote CLOB;
  v_buffer VARCHAR2(32000);

begin

With this, my trigger obviously catches an empty CLOB value. The DBMS_LOB.write... updates the column NOTES but doesn't fire my trigger. Is there anyone who can give me an idea on how to log the changes in my CLOB column (at the server level)? Please help! Received on Tue Dec 18 2001 - 12:14:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US