Gary Walker Gary Walker - 3 months ago 16
SQL Question

How to log old/new rows as XML in triggers

I would like to the the old & new rows as XML to an exceptions table when a trigger cannot succeed. I am used to using a generic

EXCEPTION WHEN OTHERS THEN
clause to log out failures, what I cannot figure out is have to capture (so I can log) the
OLD
and
NEW
pseudorows into XML.

It seems like

old_x := dbms_xmlgen.getxml('select * from OLD');


ought to work, but perhaps I am missing something simple.

Answer

You can't select from old, and there is no way to access the old values generically, I'm afraid - you have to specify old.empno, old.ename etc. one by one.

Tom Kyte has shown how to generate triggers to overcome this here on asktom.oracle.com.

Comments