Help with Delete trigger
Date: 2000/05/09
Message-ID: <8f9r8m$fgc_at_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 CEST