Re: Oracle Transaction Boundary ..

"Raghavendra S. Kothamangala" <raghavendra.kothamangala_at_gmail.com> wrote in message news:1116394205.182706.198720_at_f14g2000cwb.googlegroups.com...
> (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
>

You write a trigger for each table. I would write a script or a package that would actually write the code for me. but you can't have a generic trigger. (or performance is going to suck eggs.) You use :new.fieldname and :old.fieldname and compare them to find out if they changed. Beware if one of them is NULL, take that into account. (NULL!=NULL) (read up on triggers)

Not sure what xml has to do with anything except it is a bloated way of transfering data.(and it is now a buzz word for Java folks - when you have a hammer everything looks like a nail.)

I would have the trigger call a procedure in a package. More efficient.

Oh yes, I would start reading manuals on what the syntax on a trigger is, what types there are, and what a package is. Jim Received on Wed May 18 2005 - 00:54:33 CDT