Re: Newbie Trigger question

Hi,

1. The v$session select will return more than 1 value. It'll contain an entry for each session running. So, your choice is find a different table or pare the results of this one. You might consider a "select distinct sid from v$mystat" to get the sid your on as a subquery against either the v$session or v$session_connnect_info.
2. Sysdate has the time included. Try: 'Select to_char(sysdate, 'YYYYMMDD HHMISS') from dual;'. Greg

Clifford Buetikofer wrote:

> I'm trying to implement a trigger which traps all update and delete
> events. Basically, I have 2 final issues to resolve. Since I want to
> record the OSuser name and not the Oracle uer name, I need to do a
> select from V$SESSION to get the data value. I've tried just placing
> the select:
>
> select OSuser from v$session into local_user; {where the local_user is
> a declared varchar2}
> but the SQL keeps choking when I try to create the trigger.
>
> The second question is how can I format sysdate to include the time ?
>
> TIA,
> Cliff
>
> {see below}
>
> create or replace trigger registry_audit
> *
> ERROR at line 1:
> ORA-06550: line 8, column 19:
> PLS-00103: Encountered the symbol "FROM" when expecting one of the
> following:
> . ( , * @ % & - + / mod rem <an identifier>
> <a double-quoted string> an exponent (**) into ||
> Resuming parse at line 8, column 43.
> ORA-06550: line 25, column 6:
> PLS-00103: Encountered the symbol "IF" when expecting one of the
> following:
> a PL/SQL variable or double-quoted string
> a name
> an optional simple name
> Resuming parse at line 25, column 8.
>
> SQL> l
> 1 create or replace trigger test_audit
> 2 before update or delete on test_table
> 3 for each row
> 4 declare
> 5 user varchar2(30);
> 6 begin
> 7 if updating then
> 8 select OSuser from v$session into user;
> 9 if :old.prefix != :new.prefix then
> 10 insert into chemstrux_audit
> 11
> values('REGISTRY',user,'PREFIX',:old.prefix,:new.prefix,sysdate);
> 12 end if;
> 13 if :old.base != :new.base then
> 14 insert into chemstrux_audit
> 15
> values('REGISTRY',user,'BASE',:old.base,:new.base,sysdate);
> 16 end if;
> 17 if :old.form != :new.form then
> 18 insert into chemstrux_audit
> 19
> values('REGISTRY',user,'FORM',:old.form,:new.form,sysdate);
> 20 end if;
> 21 if :old.ck != :new.ck then
> 22 insert into chemstrux_audit
> 23 values('REGISTRY',user,'CK',:old.ck,:new.ck,sysdate);
> 24 end if;
> 25 end if;
> 26 if deleting then
> 27 insert into chemstrux_audit
> 28
> values('REGISTRY',user,'PREFIX',:old.prefix,'DELETED',sysdate);
> 29 insert into chemstrux_audit
> 30
> values('REGISTRY',user,'BASE',:old.base,'DELETED',sysdate);
> 31 insert into chemstrux_audit
> 32
> values('REGISTRY',user,'FORM',:old.form,'DELETED',sysdate);
> 33 insert into chemstrux_audit
> 34 values('REGISTRY',user,'CK',:old.ck,'DELETED',sysdate);
> 35 end if;
> 36* end;
>
> The contents of this message express only the sender's opinion.
> This message does not necessarily reflect the policy or views of
> my employer, Merck & Co., Inc. All responsibility for the statements
> made in this Usenet posting resides solely and completely with the
> sender.
Received on Wed May 20 1998 - 12:25:13 CDT