ORACLE Mutating trigger error

Question

Possible Duplicate:
ORACLE After update trigger: solving ORA-04091 mutating table error

So I have a trigger to check if a an admin has been locked out of login (if they are they will have a 1 set to temp_pw. It then send the admin a four digit pass code to unlock their account. Problem is I update the failed_logins field, incrementing it by 1 for every failed login before the trigger is called.

The rest of the trigger checks if there is an admin has a locked account before sending them an email with a pass code.

If I take out the Update Pi_admin_table set blah blah it sends the email but if I include it to insert the new pass code in the table it errors with this:

 Message: 60 ORA-00060: deadlock detected while waiting for resource
 ORA-06512: at "PI_USER_ADMIN.TR_ADMIN_LOCKOUT", line 17
 ORA-04088: error during execution of trigger
 'PI_USER_ADMIN.TR_ADMIN_LOCKOUT' UPDATE *pi_admin_table SET
 failed_logins = failed_logins + 1 where
 EMAIL='nathan@perceptive.co.uk' returning failed_logins into :bind_var

Here's my trigger:

create or replace
TRIGGER "TR_ADMIN_LOCKOUT" 
AFTER UPDATE ON PI_ADMIN_TABLE
for each row
declare
-- pragma autonomous_transaction seems to fix trigger mutation errors. 
-- Look at rewriting trigger later.
--pragma autonomous_transaction;
tempEmail varchar2(80 BYTE);
tempID varchar2(80 BYTE);
mail_host varchar2(255);
mail_port varchar2(255);
mail_from varchar2(255);
tempPW int;
begin
  SELECT EMAIL, ADMINID
    into tempEmail, tempID
    from pi_admin_table
    where TEMP_PW = :NEW.TEMP_PW;

  SELECT MAIL_HOST, MAIL_PORT, MAIL_FROM
    into mail_host, mail_port, mail_from
    from pi_settings_table;

  select dbms_random.value(1,10000)
    into tempPW
    from dual;

  if tempEmail IS NOT NULL then
    UPDATE PI_ADMIN_TABLE SET RESET_PW=round(tempPW) where adminid=tempID;
    send_mail(tempEmail,
              mail_host,
              mail_port,
              mail_from,
              'Locked Out Event',
              'Your administrator account was locked out. '|| chr(10) || chr(10) ||
              'Please use this four digit pass code next time try to log in' ||
              chr(10) || chr(10) ||  
              'Temp pass code: '|| round(tempPW) );
  end if;
END;

Simply put, your problem is caused because you cannot update the table you're triggering from in a trigger... — Ben, Aug 23 '12 at 10:46

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

Oracle does not allow code in a ROW trigger to issue a SELECT, INSERT, UPDATE, or DELETE against the table on which the trigger is defined. Your choices are to use an AUTONOMOUS TRANSACTION (but see the warning at the post referenced in @Ben's comment above) or use a COMPOUND TRIGGER.

Share and enjoy.

edited May 23 '17 at 10:24

Community

1
1

answered Aug 23 '12 at 10:50

Bob Jarvis - Слава Україні

48,992
9
77
110

score 2 · Answer 2 · answered Aug 23 '12 at 11:34

2

I would recommend you not to use trigger for that task. Encapsulate the logic you're trying to achieve in stored procedure.

answered Aug 23 '12 at 11:34

Alexander Tokarev

1,000
7
16

+1 - excellent point. Business logic like this should not be performed in a trigger. Encapsulating this logic in a PL/SQL procedure or function and calling it at the appropriate point would be a better choice. – Bob Jarvis - Слава Україні Aug 23 '12 at 16:19

ORACLE Mutating trigger error

2 Answers2