Re: Oracle Transaction Boundary ..

(Somehow the contents of my reply did not appear. Hence I am posting again.)

Hi,

The key reason for this is not auditing, but some sort of replication.

But, I have lots of discussions on auditing having very similar requirements as mine, or facing issues which I am facing.

Here is the where I am stuck now. I would again request your expertise.

I understand everyone has the context of the discussion, hence I will focus on the problem I am facing.

1. Now I have got a technique to get the tables which are part of the transaction, to insert information on the table/changes into a common table, this will help get the tables which were part of the transaction by doing a query on the transaction id (which i get from dbms_transaction package).
2. I would like to make this solution generic for all tables in the database. i.e. I would like to have a common trigger for all the tables in the database, which can get triggered after insert/update/delete operation on the tables. In this trigger, I would like to get the columns which got changed, convert it into XML, and then insert into the common table, which I am talking about in point 1.

Questions:
1. How do I get only the changed columns for the table operated on,

   in its trigger for insert/delete/update.

2. How do I get these column changes converted to XML.

   I am able to get when I specify a query like this.
-> qryctx := dbms_xmlgen.newcontext('select * from test');
-> rowData := dbms_xmlgen.getxml(qryctx);
   But is there any way to generate the changed contents without specifying

   the query.

Would appreciate some pointers on this.

Thanks in advance
-Raghu Received on Wed May 18 2005 - 00:30:05 CDT