Forum Stats

  • 3,852,310 Users
  • 2,264,090 Discussions
  • 7,905,025 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,844 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,844 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,844 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.