Re: Row ID

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 01 Sep 1999 13:18:23 GMT
Message-ID: <37cd2577.95706468@newshost.us.oracle.com>

A copy of this was sent to Fraser Boswell <Fraser.Boswell_at_spamme.ed.ac.uk> (if that email address didn't require changing) On Wed, 01 Sep 1999 10:37:11 +0100, you wrote:

>I'm recording the RowID of records in an Audit table (thanks Thomas Kyte
>for the advice), but was wondering what would destroy the RowID making

i don't remember saying to use a rowid to audit changes to a record. I use rowids in triggers to avoid mutating tables -- but i wouldn't use a rowid in the audit table -- i would use the primary key.

>it invalid. If all records in the table are copied to a new table, old
>table dropped and new table renamed back to the original, would the
>RowID be invalid for the new table?

absolutely not. if you delete a record and re-insert it, it'll get a new rowid.

>What else would cause the RowID to become out of step with the table?
>

delete and insert it and it gets a new rowid.

In Oracle8i release 8.1, if you enable row movement in a partitioned table, updating the partition key will result in a new rowid being assigned.

those are the only 2 things that change a rowid (but I would put forth that the first case above isn't really changing a rowid -- its a brand new row that never existed before if you delete/insert it).

Don't use the rowid, use the primary key.

>Thanks in advance
>
>Fraser

below is the original posting I believe to which you refer -- no rowids involved:

A copy of this was sent to Fraser Boswell <Fraser.Boswell_at_spamme.ed.ac.uk> (if that email address didn't require changing) On Wed, 18 Aug 1999 09:27:50 +0100, you wrote:

>Tried badly to explain myself last time so I'll try again. Here's the
>code body for an After Update Trigger
>
>Begin
> declare
> cursor c is
> select colname
> from my_table_columns
> where tablename = 'MY_TABLE'
> and auditflag = 'Y'; -- so cursor c brings back a list of all
> --fieldnames to be checked in case they have been
>updated
> currcolname table_columns.colname%type; -- to hold the field name
> begin
> for my_rec in c
> loop
> -- I WANT TO WRITE THE CODE BELOW
> if :NEW.currcolname != :OLD.currcolname then
> --do stuff
> end if;
> -- UP TO HERE
> end loop;
> end;
>End;
>
>Now if currcolname is Surname I need the line to read
> if :NEW.Surname != :OLD.Surname then
>instead of currcolname. So the IF statement needs to be dynamic.
>Any ideas
>
>Thanks in advance
>
>Fraser
>

This is from a previous posting of mine. It shows one method of doing this. Your "do stuff" needs to be in the check val routine. This method (below) works correctly with NULLs and the three basic datatypes of Strings, Numbers and Dates.

:new and :old are like bind variables to the trigger, they are not 'regular' variables. you cannot dynamically access them, only 'statically'.

I suggest you consider writing a stored procedure or sql*plus script to write a trigger that statically references the new/old values. For example, if you wanted to save in a table the time of update, who updated, table updated, column modified and new/old values, you could code a sql*plus script like:

create table audit_tbl
( timestamp date,

    who            varchar2(30),
    tname        varchar2(30),
    cname        varchar2(30),
    old            varchar2(2000),
    new            varchar2(2000)

)
/

create or replace package audit_pkg
as

	procedure check_val( l_tname in varchar2, l_cname in varchar2, 
						 l_new in varchar2, l_old in varchar2 );

	procedure check_val( l_tname in varchar2, l_cname in varchar2, 
						 l_new in date, l_old in date );

	procedure check_val( l_tname in varchar2, l_cname in varchar2, 
						 l_new in number, l_old in number );

end;
/

create or replace package body audit_pkg as

procedure check_val( l_tname in varchar2, l_cname in varchar2,

l_new in varchar2, l_old in varchar2 ) is
begin

	if ( l_new <> l_old or l_new||l_old is not NULL )
	then
		insert into audit_tbl values
		( sysdate, user, upper(l_tname), upper(l_cname), l_old, l_new );
	end if;

end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,

l_new in date, l_old in date )
is
begin

	if ( l_new <> l_old or l_new||l_old is not NULL )
	then
		insert into audit_tbl values
		( sysdate, user, upper(l_tname), upper(l_cname), 
		  to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ), 
		  to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ) );
	end if;

end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,

l_new in number, l_old in number ) is
begin

	if ( l_new <> l_old or l_new||l_old is not NULL )
	then
		insert into audit_tbl values
		( sysdate, user, upper(l_tname), upper(l_cname), l_old, l_new );
	end if;

end;

end audit_pkg;
/

set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
spool tmp.sql

prompt create or replace trigger aud#&1 prompt after update on &1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&1'', ''' || column_name || ''', ' ||

':new.' || column_name || ', :old.' || column_name || ');' from user_tab_columns where table_name = upper('&1') /
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp

That will build the generic table and package plus generate a trigger that would look like:

SQL> @thatscript dept

create or replace trigger aud#dept
after update on dept
for each row
begin

    audit_pkg.check_val( 'dept', 'DEPTNO', :new.DEPTNO, :old.DEPTNO);
    audit_pkg.check_val( 'dept', 'DNAME', :new.DNAME, :old.DNAME);
    audit_pkg.check_val( 'dept', 'LOC', :new.LOC, :old.LOC);

end;
/

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 01 1999 - 08:18:23 CDT