Forum Stats

  • 3,839,978 Users
  • 2,262,555 Discussions
  • 7,901,114 Comments

Discussions

NULL returned when referencing a :NEW.CLOB_COLUMN in a trigger. :OLD works

462666
462666 Member Posts: 3
edited Oct 20, 2009 1:23AM in SQL & PL/SQL
Hi All,

When inserting a CLOB value into an audit table from a trigger using the :NEW reference, the value of the CLOB is not inserted and instead returns NULL. The strange thing is that :OLD works fine.

The trigger code is:

CREATE OR REPLACE TRIGGER BCT_AU_ADT_TRG
AFTER UPDATE
ON BEN_CFG_TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN

INSERT INTO BEN_CFG_TEST_AUDIT (DATA_OPERATION, AUDIT_DATE, AUDIT_USER, VARCHAR_COLUMN, CLOB_COLUMN)
VALUES ('UPDATE NEW', SYSDATE, USER, :NEW.VARCHAR_COLUMN, :NEW.CLOB_COLUMN);

INSERT INTO BEN_CFG_TEST_AUDIT (DATA_OPERATION, AUDIT_DATE, AUDIT_USER, VARCHAR_COLUMN, CLOB_COLUMN)
VALUES ('UPDATE OLD', SYSDATE, UPPER(sys_context('USERENV','OS_USER')), :OLD.VARCHAR_COLUMN, :OLD.CLOB_COLUMN);

END;


if i change values A (varchar2) and 3 (CLOB) to B and 4 then the UPDATE NEW shows A and NULL, and UPDATE OLD shows B and 4 correctly.

Any ideas why the new CLOB value isnt carried across?

Thanks

Ben
Tagged:

Answers

This discussion has been closed.