Script to See the Before and After Update

[acct98]

Does anyone have a scrip that will provide the before and after results of an update statement?

 

[SantaMufasa]

Acct98,

To provide "one-stop shopping" for your request, I recommend a trigger on the table and the columns you wish to monitor. (Although the code appears in the sequence below for ease of understanding, you should run the sections in this sequence: 3,2,1,4,and 5; You should modify code in Sections 1 and 4 to fit your table definitions):

Section 1 - Trigger code (It audits INSERTs, UPDATEs, and DELETEs; you can modify for your needs. I chose three of the table's eleven columns for change monitoring)(I name this script "AuditGenericExample.sql"):

Create or Replace TRIGGER audit_s_emp_changes
Before insert or update or delete on test.s_emp FOR EACH ROW
BEGIN
	if inserting then
		generic_audit('test.s_emp','INSERT','All columns',:new.id,'inserting','INSERTING');
	elsif deleting then
		generic_audit('test.s_emp','DELETE','All columns',:old.id,'deleting','DELETING');
	else
		generic_audit('test.s_emp','UPDATE','LAST_NAME',:old.id,:old.last_name,:new.last_name);
		generic_audit('test.s_emp','UPDATE','FIRST_NAME',:old.id,:old.FIRST_name,:new.FIRST_name);
		generic_audit('test.s_emp','UPDATE','SALARY',:old.id,:old.SALARY,:new.SALARY);
	end if;
end;
/

Section 2 - The code for the "GENERIC_AUDIT" procedure, to which I refer in the above trigger (I name this script "AuditGeneric.sql".):

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
Create or Replace procedure generic_audit
  (table_name      in varchar2,
   action_type     in varchar2,
   updated_column  in varchar2, 
   pk              in varchar2, 
   old_value       in varchar2, 
   new_value       in varchar2)
  is
	userinfo	varchar2(200);	
  begin
     IF (old_value is null and new_value is not null) or
	(old_value is not null and new_value is null) or
	(old_value <> new_value) THEN
	select osuser||' on '||machine||'(as '||user||')' into userinfo
		from sys.sessions where audsid = userenv('sessionid');
        INSERT INTO auditor
               VALUES (table_name, SYSDATE,USERINFO,action_type, 
               pk,updated_column,old_value,new_value);
     END IF;
  END;
/

Note: the above stored procedure (as written) depends upon SELECT privileges on the SYS.SESSIONS table.

Section 3 - Code to produce the Auditor table (into which the above code INSERTs)(I name this script "AuditTableCreate.sql"):

create table auditor
	(table_name		varchar(30)
	,action_datetime	date
	,perpetrator		varchar(200)
	,action_type		varchar(10)
	,pk			varchar(100)
	,which_column		varchar(30)
	,old_value		varchar(4000)
	,new_value		varchar(4000)
	)
/

Section 4 - Sample changes to the S_EMP table for which we are monitoring changes:

insert into s_emp (id, last_name, first_name, salary) values (28,'98','Acct',9500);
commit;
update s_emp set salary = 9600 where id = 28;
commit;
update s_emp set last_name = '99' where id = 28;
commit;
delete s_emp where id = 28;
commit;
/

Section 5 - Code to display formatted contents of the AUDITOR table (I name this script "AuditReport.sql".):

col a heading "Table|Name" format a15
col b heading "Date/Time|of Change" format a11
col c heading "Identity of Changer" format a20
col d heading "Action|Type" format a7
col e heading "Primary|Key or|Unique|ID" format a10
col f heading "Column|Name" format a20
col g heading "Old Value" format a20
col h heading "New Value" format a20
set echo off
set linesize 200
set pagesize 500
select	 TABLE_NAME	a
	,to_char(ACTION_DATETIME,'dd-MON-yyyy hh24:mi:ss') b
	,PERPETRATOR	c
	,ACTION_TYPE	d
	,PK		e
	,WHICH_COLUMN	f
	,OLD_VALUE	g
	,NEW_VALUE	h
from auditor
order by action_datetime
/
(Note: On Tek-Tips, the following output appears with undesirable display wrapping; for proper viewability, copy and paste to a file that does not have right margin limitations of less than 91 character positions.)
                                                    Primary
                                                    Key or
Table      Date/Time                         Action Unique  Column
Name       of Change   Identity of Changer   Type   ID      Name        Old Value New Value
---------- ----------- --------------------- ------ ------- ----------- --------- ---------
test.s_emp 22-MAR-2004 dhunt on TFMOBILE2709 INSERT 28      All columns inserting INSERTING
            09:39:29   (as TEST)

test.s_emp 22-MAR-2004 dhunt on TFMOBILE2709 UPDATE 28      SALARY      9500      9600
            09:39:29   (as TEST)

test.s_emp 22-MAR-2004 dhunt on TFMOBILE2709 UPDATE 28      LAST_NAME   98        99
            09:39:30   (as TEST)

test.s_emp 22-MAR-2004 dhunt on TFMOBILE2709 DELETE 28      All columns deleting  DELETING
            09:42:19   (as TEST)

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:06 (22Mar04) UTC (aka "GMT" and "Zulu"), 10:06 (22Mar04) Mountain Time)

 

[acct98]

Mufasa,

The table already exists--I'm just changing the values based on a specific criteria. I was just hoping to find out a simple way to do this. I can creat a file before the update and after the update. I guess there is no easy way. Thanks for your help.

Regards.

 

[SantaMufasa]

Acct98,

Perhaps I misunderstood what you wanted. Are you saying that you are going to make a bunch of changes to your table and you want to display the pre-change values before you make the changes, then make the changes, then display the post-change values? If so, just produce a SELECT statement that the displays the row values that match your UPDATE's "WHERE" clause.

If however, you want to do what you said you want to do in your original post (a "script that will provide the before and after results of an update statement") then the best way to do that (change by change) is with an "update trigger."

When you say, "The table already exists...", that, too, was my presumption. (Had you posted listing of the table name and focus values from your application, I would have coded with those.) That is why I said, "You should modify code in Sections 1 and 4 to fit your table definitions." When you plug into those sections the specifics for your existing table, then if the rest of the code is in place, it automatically generates an AUDIT of your before and after values. When you say, "I was just hoping to find out a simple way to do this", what could be simpler than having working code that does precisely what you want to do, delivered to you free of charge?

I believe you have not tried the posted solution, above. It should take only a few minutes to modify Sections 1 and 4 to your table, then run sections 3,2,1,4,and 5. There is no risk to your application. The code creates a TRIGGER, a TABLE, and a FUNCTION, all of which you can DROP with no impact to your application if you don't care for the solution.

But it's obviously up to you. Let me know if I'm misunderstanding or missing something important.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:38 (22Mar04) UTC (aka "GMT" and "Zulu"), 14:38 (22Mar04) Mountain Time)

 

[acct98]

I will give it a shot -- I will let you know how it turns out. Thanks.

 

[SantaMufasa]

I'm bumping this thread to the top so that Knitwit can review it easier, to resolve his change-audit requirements.
********************************************************************************************

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

...and the thread needs to be about 6 characters wider to avoid wrapping the output. So here are enough "*" to make that happen:
****************************************************************************************************

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

You may also read about so-called flashback queries. The allow to query table as it was some time before.

Regards, Dima

http://seminihin.narod.ru