2 Replies Latest reply: Sep 25, 2013 10:51 AM by CraigB RSS

    Get user name trigger

    Azamshareef

      Dear All,

       

      I have a problem with below trigger. Can anyone help me.

       

      CREATE TABLE MASTER_TABLE(NAME VARCHAR2(1000),NUM NUMBER);

       

       

       

      CREATE TABLE DUP_TABLE(NO NUMBER,OLD_NAME VARCHAR2(1000),NEW_NAME VARCHAR2(1000),UPDATE_DATE DATE,username varchar2(1000));

       

       

       

      CREATE OR REPLACE TRIGGER HIST

      after UPDATE OF NAME,num

         ON MASTER_TABLE

         FOR EACH ROW    

      DECLARE

        PRAGMA AUTONOMOUS_TRANSACTION;

      USR VARCHAR2(1000); 

      BEGIN

      SELECT USER INTO U FROM dual;

         INSERT INTO DUP_TABLE

         (NO,OLD_NAME,NEW_NAME,update_date,username )

         VALUES

         (:new.Num,:old.name,:new.name,sysdate,USR);

           COMMIT;

           END ;

         

           SHOW ERR

       

      i want to put user_name(value) into usr(variable) from fnd_user table. How do i correct the above trigger though it is successfully running but it appears user. Is there any concept for calling triggers with parameters?