Finding modified values on update statements without using :old :new

[alorincz]

in an update trigger i want to find these fields.

thx for u're help

[alorincz]

I need to find the modified columns, without comparing the :new and ld values for all the columns in a table.

Thx again

[Shestakov]

In after/before update triggeres no way without comparing values of fields.

[alorincz]

Yes, I know. I was refering to a database trigger which can be row a row level trigger, and not necesarily a statement level trigger...

Thx,

[chrisrlong]

Obviously. If you were talking statement level, you wouldn't even have an ld and :new table. And as was stated, there is practically no other way to do it.

'Practically' means that an impractical solution does exist. That is to create a separate UPDATE trigger for each column (UPDATE of ), but otherwise, ld and :new are your best bet.

I'm wondering what the issue here is. Are there tons of columns? Why do you need to know which ones changed? Why can't the app tell you this? etc.

- Chris

[alorincz]

The problem is that, we have more than one databases in different locations, but some of them are Oracle Standard edition, which does not permit (as I know, maybe I'm wrong) replication.
So we will create a synchronizing procedure, which will do the same thing.
Now if it's an insert or delete statement it's somehow easy, but in case of update we want to pass to the procedure only the modified columns, and the modified values. I've looked for some system views, but without any luck... So if you have ny idea it would be greatly appreciated...

Thx...

[ales]

Originally posted by alorincz
... some of them are Oracle Standard edition, which does not permit (as I know, maybe I'm wrong) replication.

The Basic Replication is supported by Standard Edition. You can create read-only snapshots on remote sites and let Oracle to replicate data automatically.
The Advanced (multi-master) Replication is part of EE only.

[alorincz]

That's our problem.... We need multi-master replication, so that every database should write his own data on every other database. And that's what we are trying to simulate.

[ales]

Well ... great challenge! Good luck!

[alorincz]

Thx...

We've created some audit table, and some linux functions that will do the work, but we need to find the modified columns, so that we won't have to synchronize all the columns in a table.
I was thinking, that it might be some Oracle system table / view from which we could find the modified columns. I'm looking in the Oracle documentation, but so far without too much luck...