Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Question Unanswered: After Update Trigger In Oracle

    my trigger defination is:

    CREATE OR REPLACE TRIGGER COUNT_OF_LOGINS AFTER update
    ON T_SEC_M_USERMASTER
    DECLARE userid VARCHAR2(50);
    dt VARCHAR2(50);
    i NUMBER;
    currdate VARCHAR2(50);
    BEGIN
    IF UPDATING THEN

    select USER_V_USERID into userid from T_SEC_M_USERMASTER where USER_LASTLOGIN = (select max(USER_LASTLOGIN)from T_SEC_M_USERMASTER);
    SELECT USER_LASTLOGIN INTO dt FROM T_SEC_M_USERMASTER WHERE USER_V_USERID=userid;
    SELECT sysdate INTO currdate FROM DUAL;
    select COUNT(*) INTO i from T_SEC_T_LOGIN_COUNT WHERE USERID=userid AND LOGIN_DT=currdate;
    IF(i=0) THEN
    INSERT INTO T_SEC_T_LOGIN_COUNT(USERID,LOGIN_DT,LOGIN_COUNT) VALUES(userid,currdate,'1');

    ELSE
    UPDATE T_SEC_T_LOGIN_COUNT SET LOGIN_COUNT=LOGIN_COUNT+1,LOGIN_DT=currdate WHERE USERID=userid;
    END IF;
    END IF;
    END;

    means i have one table T_SEC_M_USERMASTER in that i have one column
    named USER_V_USERID ,USER_LASTLOGIN etc.
    Another table named T_SEC_T_LOGIN_COUNT having columns
    USER_ID,LOGIN_DT AND LOGIN_COUNT.

    Now what i am doing is ,when user logins the application (java application),
    i have written an update statement in it's coressponding action that updates
    the login date(USER_LASTLOGIN ) in the T_SEC_M_USERMASTER coressponding to that user.then on this update above trigger fires that
    inserts the USER_ID,LOGIN_DT AND LOGIN_COUNT into the
    T_SEC_T_LOGIN_COUNT table.then if the same user logins again on the same date then it increments the LOGIN_COUNT in the table.
    problem occurs when another user on the same date tries to login the application,then due to this st.

    select USER_V_USERID into userid from T_SEC_M_USERMASTER where USER_LASTLOGIN = (select max(USER_LASTLOGIN)from T_SEC_M_USERMASTER);
    Error comes that exact fetch more the one record........
    Means every time i want to pass the id of the user that logins in the application to the trigger so that it can make a entry in the T_SEC_T_LOGIN_COUNT table coressponding to that user.
    I have tried the concept of new and old table as well but then the following
    error comes:

    ORA-04091: table DGST_TEST.T_SEC_M_USERMASTER is mutating, trigger/function may not see it ORA-06512: at "DGST_TEST.COUNT_OF_LOGINS", line 14 ORA-04088: error during execution of trigger 'DGST_TEST.COUNT_OF_LOGINS'

    plz give a solution
    ques123

  2. #2
    Join Date
    Mar 2008
    Posts
    89
    Oracle says:
    ORA-04091: table string.string is mutating, trigger/function may not see it
    Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
    Action: Rewrite the trigger (or function) so it does not read that table.
    Without trying to understand your trigger entirely, to me it sounds like your trigger is attempting to do something on the same table that the firing event (Update) still has a lock on!
    If that's the case your Java application is written in such a way that it either requires the table to stay locked for itself after the Update, or simply refuses to release that lock.

    Any other ideas though?
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Bad Trigger

    What did you not understand about the ORA-04091 error?:
    Action: Rewrite the trigger (or function) so it does not read that table.
    We recommend you read the fine Oracle® Database Application Developer's Guide - Fundamentals manual.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Read up on mutating tables. You may not alter or insert or select on the same table that your trigger is on except if it is an instead trigger, which can only be on a view.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •