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?