Re: trigger advice please

USER is a reserved word, you cannot have a coulmn called user. We have a column called moduserid and this statement is part of our trigger.

select user into :new.modappuserid from dual;

here is our trigger if you want to see the code:

create or replace trigger owner.tbi_t_detail before insert
on owner.t_detail
referencing old as old new as new
for each row
begin
  if INSERTING then
    if :new.uniqueid is null then
      select seq_t_detail_uid.nextval into :new.uniqueid from dual;     end if;
    if :new.code is null then
      select seq_t_detail_code.nextval into :new.code from dual;     end if;
    if :new.removed is null then
      :new.removed := 'N';
    end if;
    select user into :new.creappuserid from dual;     select userenv('terminal') into :new.crewsid from dual;     :new.credttm := sysdate;
    select user into :new.modappuserid from dual;     select userenv('terminal') into :new.modwsid from dual;     :new.moddttm := sysdate;
  end if;
end;

HTH,
   Michael Heindel, AAM, OCP DBA

In article <94ochv$k6u$1_at_nnrp1.deja.com>,   Glen A. Stromquist <glenstr_at_my-deja.com> wrote:
> Ok, I have a table that has a few columns in it that I want to monitor
> for changes. I created a table for a trigger to insert rows in when
> these columns are updated, in this table I have two columns for each
> one column that I want tracked in the other table, one of them for the
> old value, one for the new.
>
> Following is the trigger I wrote to accomplish this:
>
> create or replace trigger tr_aud_table
> before update on schema.table_name
> for each row
> begin
> insert into schema.audit_changes
> (ref_number,
> change_date,
> user,
> old_col1,
> old_col2,
> old_col3)
> VALUES
> (:OLD.ref_NUMBER,
> SYSDATE,
> USER,
> OLD:col1,
> OLD:col2,
> OLD:col3)
> UPDATE schema.AUDIT_CHANGES
> SET
> new_col1 = NEW:col1,
> new_col2 = NEW:col2,
> new_col3 = NEW:col3
> WHERE ref_number = OLD:ref_number
> END;
>
> will this work?
> does "USER" pick up the user changing the table?
>
> thanks in advance
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 25 2001 - 06:46:34 CST