Re: Trigger Help

From: TurkBear <johng_at_nospam.mm.com>
Date: Mon, 07 Dec 1998 17:35:54 GMT
Message-ID: <366c1178.10332196@news2.mm.com>

"Jeffrey A. Thompson" <thompsonja_at_phibred.com> wrote:

>I'm trying to write a trigger to update a 'revisor_name' column in a table.
>The problem is that we are using powerbuilder and signing onto ORACLE 7.3
>with a generic userid. I noticed that osuser in the v$session has the
>username I want. Does anyone have an example of trigger that does this?
>
>

Here is the skeleton of a trigger I designed to monitor and track inserts into one of my tables:

---

AFTER INSERT,UPDATE,DELETE TRIGGER 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;

---

The 3 select statements will identify the user regardless of the Oracle login name used....

Hope it helps,
John Greco

To reply please remove the 'nospam' part of the address Received on Mon Dec 07 1998 - 11:35:54 CST