help with delete trigger

From: Gary Knopp <gknopp_at_ascend.com>
Date: 2000/05/09
Message-ID: <8f9r04$fes@nntpa.cb.lucent.com>#1/1

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?

/***************************************************************************
*****/

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; Received on Tue May 09 2000 - 00:00:00 CDT