Newbie Trigger question

From: Clifford Buetikofer <clifford_buetikofer_at_merck.com>
Date: Wed, 20 May 1998 12:35:51 -0400
Message-ID: <35630667.BFB2536C@merck.com>

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 - 11:35:51 CDT