In article <8f9r04$fes_at_nntpa.cb.lucent.com>,
"Gary Knopp" <gknopp_at_ascend.com> wrote:
>
> I have an application that accesses an Oracle database
> through a SINGLE USER ACCOUNT. I need to store an audit
> trail for data changes for each user of the application.
> Each table that needs an audit trail has a username column.
> For 'Selects' and 'Inserts' I send the username as part of
> sql syntax and use a trigger to load the audit table.
> For deletes I execute a procedure before the delete to
> set the username in a package varaiable, and then try to
> read the package variable in from a trigger. I can't seem
> to get this portion to work. Does the procedure\function
> that needs access to the public variable need to part of
> the package?
>
> What am I doing wrong?
>
> I created a test script with my problem. Any help
> would be greatly appreciated?
>
please define "can't seem to get this portion to work". I've run your
script below in 7.1.6, 7.3.4, 8.0.3, 8.1.5 and it "worked" each time.
the deletes were audited as if they came from "Joe". no error
messages -- no apparent logic errors.
what should we look for?
> /*********************************************************************
> *****/
>
> create or replace package util
> as
> username varchar2(10);
> procedure setUsername(who IN varchar2);
>
> end util;
>
> /
> create or replace package body util
> as
> procedure setUsername(who IN varchar2)
> is
> begin
> util.username := who;
> end;
> end util;
> /
> drop table testme;
> /
> create table testme
> (
> f1 varchar2(20),
> f2 varchar2(20),
> f3 varchar2(20),
> username varchar2(10)
> )
> /
> drop table history;
> /
> create table history
> (
> chgDt date default sysdate,
> username varchar2(10),
> xaction varchar2(1),
> tabName varchar2(20),
> colName varchar2(20),
> value varchar2(255)
> );
>
> create or replace trigger trgTest
> before insert or update or delete on testme
> for each row
> declare
> chgDt date;
> username varchar2(10);
> begin
> chgDt:= sysDate;
> if inserting then
> insert into
> history(chgDt,username,xaction,tabName,colName,value)
> values(chgDt,:new.username,'I','testme','f1',:new.f1);
> insert into
> history(chgDt,username,xaction,tabName,colName,value)
> values(chgDt,:new.username,'I','testme','f2',:new.f2);
> insert into
> history(chgDt,username,xaction,tabName,colName,value)
> values(chgDt,:new.username,'I','testme','f3',:new.f3);
> elsif updating then
> if (:new.f1 <> :old.f1) then
> insert into history
> (chgDt,username,xaction,tabName,colName,value)
> values(chgDt,:new.username,'U','testme','f1',:new.f1);
> end if;
> if (:new.f2 <> :old.f2) then
> insert into history
> (chgDt,username,xaction,tabName,colName,value)
> values(chgDt,:new.username,'U','testme','f2',:new.f2);
> end if;
> if (:new.f3 <> :old.f3) then
> insert into history
> (chgDt,username,xaction,tabName,colName,value)
> values(chgDt,:new.username,'U','testme','f3',:new.f3);
> end if;
> else
> --ERROR occurs here with 'util.username'
> insert into
> history(chgDt,username,xaction,tabName,colName,value)
> values(chgDt,util.username,'D','testme','f1',:old.f1);
> insert into
> history(chgDt,username,xaction,tabName,colName,value)
> values(chgDt,util.username,'D','testme','f2',:old.f2);
> insert into
> history(chgDt,username,xaction,tabName,colName,value)
> values(chgDt,util.username,'D','testme','f3',:old.f3);
> end if;
> end;
>
> /
> insert into testme values('a','b','c','gknopp');
> /
> update testme set f1='a4';
> /
> execute util.setUsername('Joe');
> /
> delete from testme where f1='a4';
> /
> select * from testme;
> /
> select to_char(chgDt,'DD-MON-YY HH24:MI:SS')as
> ChgDt,username,xaction,tabName,colName,value from history;
>
>
--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue May 09 2000 - 00:00:00 CDT
