Auditing Trigger thanks and solution

From: TurkBear <johng_at_mm.com>
Date: Thu, 04 Jun 1998 17:09:12 GMT
Message-ID: <3577d24d.4009755@news2.mm.com>

Thanks to those of you who responded to my request for help in developing a trigger for audit insert,updates and deletes from a table,,,

My task was complicated by the fact that , in many of my apps, many users are using the same Oracle LoginID and I needed to capture the Network Login name for effective auditing....( system is Netware 4.11,Oracle 7.3.x.x ) - For those who would like to try it, here is my solution -

---

AFTER INSERT,UPDATE,DELETE TRIGGER ( as a statement )

Declare
oname varchar2(100);
unam varchar2(100);
usid number;
Begin
select user into unam from global_name; select max(sid) into usid from v$mystat; select osuser into oname from v$session where username = unam and

       sid = usid;
if inserting then
insert into audit_lid values('Insert',sysdate,oname,'Future ') ;

elsif updating then
insert into audit_lid values('Update',sysdate,oname,'Future '); elsif deleting then
insert into audit_lid values('Delete',sysdate,oname,'Future '); else
raise_application_error(-20100,'Undefined Action...'); end if;
end;

---

In the above code the 'select max(sid) from v$mystat...' gets me a unique identifier for the user's session making the change, which I then use to select the osuser from v$session.

The text entry 'Future' is for future enhancements - that field will eventually be used to contain the changed value.

Thanks again for your ideas....

John Greco
Oracle DBA
Email to john.greco_at_dot.state.mn.us Received on Thu Jun 04 1998 - 12:09:12 CDT