Gary Walker Gary Walker - 1 year ago 89
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

clause to log out failures, what I cannot figure out is have to capture (so I can log) the
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download