Trying to log the user name of the person making a change. 1

[jcarrott]

I started out with this code,

CREATE OR REPLACE TRIGGER PROCURE."LAWSON_PRICE_CHG_TRG"
  AFTER UPDATE OF "BASE_COST"
  ON "LAWSON"."POVAGRMTLN"
  REFERENCING NEW AS N OLD AS O
  FOR EACH ROW

BEGIN
  IF ( NOT (:O.BASE_COST = :N.BASE_COST)) 
  THEN
        INSERT into PROCURE.PRICE_CHG_LOG 
        (procure_group, ven_agrmt_ref, item, base_cost_old, base_cost_new, user_id, effective_dt) 
        values 
  (:o.procure_group, :o.ven_agrmt_ref, :o.item, :o.base_cost, :n.base_cost, sys.login_user, sysdate);

  END IF;
END;

This worked as a trigger but it did not store the user id.

I added some code to it,

CREATE OR REPLACE TRIGGER PROCURE."LAWSON_PRICE_CHG_TRG"
  AFTER UPDATE OF "BASE_COST"
  ON "LAWSON"."POVAGRMTLN"
  REFERENCING NEW AS N OLD AS O
  FOR EACH ROW
DECLARE
  myuser varchar2(30);

BEGIN

  SELECT osuser
  into myuser
  from sys.v_$session;

  IF ( NOT (:O.BASE_COST = :N.BASE_COST)) 
  THEN
        INSERT into PROCURE.PRICE_CHG_LOG 
        (procure_group, ven_agrmt_ref, item, base_cost_old, base_cost_new, user_id, effective_dt) 
        values 
(:o.procure_group, :o.ven_agrmt_ref, :o.item, :o.base_cost, :n.base_cost, myuser, sysdate);
  END IF;
END;

This caused major issues with the system.

I am just trying to caputure the user id and store it, does anybody have any ideas?

 

[Dagon]

Have you tried just USER e.g.

select user from dual

 

[Dagon]

Another option if you really want the OS user rather than Oracle user:

a) Write a small package which basically just consists of a variable e.g. mypackage.osuser_value.
b) Write a logon trigger which does:

SELECT osuser
into mypackage.osuser_value
from sys.v_$session

You should then just be able to reference mypackage.osuser_value in your triggers, with next to no performance overhead.

 

[LKBrwnDBA]

What about:

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
  FROM DUAL;
-- Or --
SELECT SYS_CONTEXT ('USERENV', 'OS_USER') 
  FROM DUAL;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb