This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,573 Users
  • 2,269,776 Discussions
  • 7,916,824 Comments

Discussions

After Update Trigger Not Triggering

Abk
Abk Member Posts: 122
edited Nov 4, 2010 5:35AM in SQL & PL/SQL
Dear all,
Pl help me to fix the error this trriger.


CREATE TRIGGER AUDIT_DEV.trg1
AFTER DELETE OR UPDATE OF EMP_STATUS ON AUDIT_DEV.AUDIT_PERSONS
declare
AUDIT_PERSONS.EMP_USER_MODIFIED%TYPE;
AUDIT_PERSONS.EMP_DATE_MODIFIED%TYPE;
AUDIT_PERSONS.EMP_SK%TYPE;
AUDIT_PERSONS.EMP_ID%TYPE;
AUDIT_PERSONS.EMP_NAME%TYPE;
EMP_STATUS_OLD VARCHAR2(10);
EMP_STATUS_NEW VARCHAR2(10);
EMP_PERSONS.EMP_RESIGNATION_DATE%TYPE;
V_$SESSION.OSUSER%TYPE;
V_$SESSION.MACHINE%TYE;
V_$SESSION.USERNAME%TYPE;
BEGIN
select
sid,serial#,username,osuser,machine,logon_time into sid,serial#,username,osuser,machine,logon_time
from v$session where sid=(select sid from v$mystat where rownum=1);

INSERT INTO AUDIT_PERSONS_LOG
(EMP_USER_MODIFIED,EMP_DATE_MODIFIED,EMP_SK,EMP_ID,EMP_NAME,EMP_RESIGNATION_DATE,EMP_STATUS_OLD,EMP_STATUS_NEW,osuser,db_user,machine) VALUES
(
EMP_USER_MODIFIED,EMP_DATE_MODIFIED,EMP_SK,EMP_ID,EMP_NAME,EMP_RESIGNATION_DATE,:old.EMP_status,:new.EMP_status,osuser,db_user,machine
)
END;
/

Error report:
ORA-04082: NEW or OLD references not allowed in table level triggers
04082. 00000 - "NEW or OLD references not allowed in table level triggers"
*Cause: The trigger is accessing "new" or "old" values in a table trigger.
*Action: Remove any new or old references.

Edited by: Abk on Nov 4, 2010 11:09 AM

Best Answer

Answers

  • Saubhik
    Saubhik Member Posts: 5,957 Gold Crown
    CREATE TRIGGER AUDIT_DEV.trg1
    AFTER DELETE OR UPDATE OF EMP_STATUS ON AUDIT_DEV.AUDIT_PERSONS
    FOR EACH ROW
    Saubhik
  • 789895
    789895 Member Posts: 753
    edited Nov 3, 2010 5:57AM
    Hi,

    Try this
    CREATE TRIGGER AUDIT_DEV.trg1
    AFTER DELETE OR UPDATE OF EMP_STATUS ON AUDIT_DEV.AUDIT_PERSONS 
    
    Include these statements
    
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW or FOR EACH STATEMENT
    cheers

    VT
  • Abk
    Abk Member Posts: 122
    edited Nov 4, 2010 1:13AM
    Thanks for your replies Saubhik/VT,

    now my trigger is compiled. but is not triggering. pl help me to resolve .....

    create or replace
    TRIGGER AUDIT_DEV.trg2
    AFTER DELETE OR UPDATE OF EMP_STATUS ON AUDIT_DEV.AUDIT_PERSONS
    FOR EACH ROW
    declare

    OSUSER varchar2(30);
    MACHINE varchar2(30);
    logon_time date;
    db_user varchar2(30);
    USERNAME VARCHAR2(30);
    EMP_USER_MODIFIED AUDIT_PERSONS.EMP_USER_MODIFIED%TYPE;
    EMP_DATE_MODIFIED AUDIT_PERSONS.EMP_DATE_MODIFIED%TYPE;
    EMP_SK AUDIT_PERSONS.EMP_SK%TYPE;
    EMP_ID AUDIT_PERSONS.EMP_ID%TYPE;
    EMP_NAME AUDIT_PERSONS.EMP_NAME%TYPE;
    EMP_RESIGNATION_DATE AUDIT_PERSONS.EMP_RESIGNATION_DATE%TYPE;

    BEGIN

    select username,osuser,machine,logon_time into db_user,osuser,machine,logon_time from v$session where sid=(select sid from v$mystat where rownum=1);
    INSERT INTO AUDIT_DEV.AUDIT_PERSONS_LOG (EMP_USER_MODIFIED,EMP_DATE_MODIFIED,EMP_SK,EMP_ID,EMP_NAME,EMP_RESIGNATION_DATE,EMP_STATUS_OLD,EMP_STATUS_NEW,osuser,db_user,machine)
    VALUES(EMP_USER_MODIFIED,EMP_DATE_MODIFIED,EMP_SK,EMP_ID,EMP_NAME,EMP_RESIGNATION_DATE,:old.EMP_status,:new.EMP_status,osuser,db_user,machine );
    COMMIT;
    END;
    /


    09:59:06 [email protected]>UPDATE AUDIT_DEV.AUDIT_PERSONS SET EMP_STATUS='TEST' WHERE EMP_ID='4234';

    EMP_STATUS
    ---------------
    TEST

    1 row selected.

    Elapsed: 00:00:00.01
    10:00:03 [email protected]>commit;

    Commit complete.

    Elapsed: 00:00:00.01
    10:00:17 [email protected]>select * from AUDIT_persons_log;

    no rows selected

    Elapsed: 00:00:00.00

    10:00:17 [email protected]>

    Edited by: Abk on Nov 4, 2010 10:42 AM
  • Saubhik
    Saubhik Member Posts: 5,957 Gold Crown
    Remove COMMIT from the trigger body.
    Saubhik
  • Abk
    Abk Member Posts: 122
    edited Nov 4, 2010 4:37AM
    i had removed the commit; , still not triggering. Pl someone help me to solve...

    Edited by: Abk on Nov 4, 2010 2:06 PM

    Edited by: Abk on Nov 4, 2010 2:06 PM
  • Saubhik
    Saubhik Member Posts: 5,957 Gold Crown
    Answer ✓
    What is the result of
    SELECT * FROM user_triggers
    WHERE trigger_name='your_trigger_name';
    Specially note the status column.
  • Abk
    Abk Member Posts: 122
    Thanks for your reply, Saubhik

    here i found in the query output column_name is blank.




    OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_OWNER BASE_OBJECT_TYPE TABLE_NAME
    ------------ ------------------------------ ---------------- -------------------- ------------------------------ ---------------- ---------------------------------
    DEV TRG2 AFTER EACH ROW UPDATE OR DELETE DEV TABLE AUDIT_PERSONS




    COLUMN_NAME REFERENCING_NAMES
    -------- -------------------- --------------------------------------
    REFERENCING NEW AS NEW OLD AS OLD

    STATUS DESCRIPTION ACTION_TYPE TRIGGER_BODY
    ----------------- -------- -------------------------------------------------- ----------- ---------------------
    ENABLED DEV.trg2 PL/SQL declare
    AFTER DELETE OR UPDATE OF PRS_STATUS ON DEV.AUDIT
    PERSONS -- OSUSER varchar2(30);
    FOR EACH ROW -- MACHINE varchar2(30);
    -- logon_time date;
    -
  • 635471
    635471 Member Posts: 1,937
    Instead of selecting values into variables and then inserting using a values clause, can you not just run an insert statement that selects the values you want?
This discussion has been closed.