Re: old value and new value

In article <Pine.NXT.3.90.950504082512.12711A_at_iluvatar>, John Jones <john_at_iluvatar.tip.duke.edu> wrote:
> I know that you can use old.column and new.column in a trigger to
>compare updates, but can you use the same convention in a stored proc.
>We are trying to write a proc that will add records to a transaction log
>and need to compare if the values change for each column. Any body have
>any ideas or suggestions on this. Reply here or to e-mail.
>

SQL*Trax, the redo log analyzer for Oracle7, can display both the before and after image values of every column affected by a DML statement.

For example, an update of an employee's salary from $45,000 to $48,000 would be displayed as...

05/07/95 13:00:52      BEGIN TXN
05/07/95 13:00:52 UNDO UPDATE SYS.EMP (ROWID='00000c95.0000.0001')
05/07/95 13:00:52 UNDO   SALARY=45000
05/07/95 13:00:52      UPDATE SYS.EMP (ROWID='00000c95.0000.0001')
05/07/95 13:00:52        SALARY=48000
05/07/95 13:00:52      COMMIT

A delete of that same employee record would be displayed as...

05/07/95 13:00:56      BEGIN TXN
05/07/95 13:00:56 UNDO INSERT INTO SYS.EMP (ROWID='00000c95.0000.0001')
05/07/95 13:00:56 UNDO   EMPNO=1
05/07/95 13:00:56 UNDO   NAME='John Doe'
05/07/95 13:00:56 UNDO   SALARY=48000
05/07/95 13:00:56      DELETE FROM SYS.EMP WHERE ROWID = '00000c95.0000.0001';
05/07/95 13:01:00      COMMIT

Note there is enough information displayed to re-insert the employee record should the deletion have been made in error. Also note that, since this info is already being maintained by the Oracle RDBMS in the redo logs, there is no additional overhead imposed upon the application (unlike the trigger solution).

If you would like further details about this product, I can be reached at 415-851-9576, or send E-Mail to smartin_at_turnstone.com.

SCott. Received on Sun May 07 1995 - 00:00:00 CEST