Re: Row ID
Date: 1999/09/01
Message-ID: <37cd2577.95706468_at_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
_at_tmp
That will build the generic table and package plus generate a trigger that would look like:
SQL> _at_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 CorporationReceived on Wed Sep 01 1999 - 00:00:00 CEST