Trigger to update after insert on the same row

Question

I have a function my_func, the input of this function is one of the column in the same row after insertion, how to achieve this.

I have created the table using,

create table sample_trigger_hash ( INDEXID number(19,0) ,
     XMLCOLUMN XMLTYPE ,
     CHECKFIELD RAW(30)
) XMLTYPE COLUMN XMLCOLUMN STORE AS BINARY XML;

I am thinking of an insertion statement from DBD::Oracle in perl , with just the first two fields.

The following is my trigger,

CREATE OR REPLACE
TRIGGER hash_trigger
AFTER INSERT ON sample_trigger_hash
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   SELECT my_hash(EXTRACT(:OLD.XMLCOLUMN ,''))
         INTO :NEW.CHECKFIELD
     FROM dual;
END hash_trigger;

Am getting the error,

ORA-04084: cannot change NEW values for this trigger type
04084. 00000 -  "cannot change NEW values for this trigger type"
*Cause:    New trigger variables can only be changed in before row
           insert or update triggers.
*Action:   Change the trigger type or remove the variable reference.

But my requirement is to get the XMLType value after insertion, how to achieve this. If I use CLOB storage option for the XMLType, I am getting the same hash value before storing and after storing. But this is not the case when I store it as binary XML. (an XSI section is added to the binary version) More details in this other question.

You're hashing `:OLD.XMLTYPE`, so that should already be the stored value; is that right, or do you really want the `NEW` value? Showing the table definition, insert statement and data types of any variables might be helpful. There might be an implicit conversion you can avoid, for example. Do you have the same issue if `CHECKFIELD` is a virtual column? — Alex Poole, May 29 '14 at 07:14
If I create that table, change the trigger to `before insert` and have it just do `:NEW.CHECKFIELD := my_hash(:NEW.XMLCOLUMN);`, and insert a value... then I get the same hash value after inserting. Can't show in a Fiddle as it doesn't support dbms_crypto. It's possible this is a problem with your 11.2.0.1 version, but hard to tell - would be useful to be able to reproduce it. — Alex Poole, May 29 '14 at 09:18
@AlexPoole yes, I use 11.2.0.1 , IF I specify the storage option as clob in 11.2.0.1 the hashing values are same, but not when when I store it as binary xml. — Nishanth Lawrence Reginold, May 29 '14 at 09:46

score 0 · Answer 1 · answered May 29 '14 at 05:40

0

Your requirement doesn't make sense. You want to include the hashed value in the newly inserted row, so put the function in a before insert trigger.

answered May 29 '14 at 05:40

APC

144,005
19
170
281

I am using binary xmltype, the hashing value is different before storing and after storing that's why I wanted to use after insert. If before insert is sufficient for me I would have rather called the function directly while inserting than opting for a trigger. – Nishanth Lawrence Reginold May 29 '14 at 05:44
Why is it different? At what point in the insert does it change? – APC May 29 '14 at 05:51
If I use clob storage option for the xmltype, I am getting the same hash value before storing and after storing. But this is not the case when I store it as binary xml. – Nishanth Lawrence Reginold May 29 '14 at 06:01

Trigger to update after insert on the same row

1 Answers1