Trigger ora-04091 error mutating table

[amillia]

I am trying to write a trigger that when the user_info.ui_lastlogin is updated then I want the data sent to another table. The point of this is that the user_info table is only keeping up with the last time a person logged in and their user name. We need every time a person logs in and their user name. So we thought if we captured data before the data changed again then this would give us our requirement. But I suck at triggers and this one is giving me a strange error ORA-04091 (mutating table) AND ORA-06512 AND ORA-04088. Could someone please help me.


Here is the code so far:

CREATE OR REPLACE TRIGGER MCEMGR.ECAM_TRIGGER
AFTER INSERT
ON MCEMGR.USER_INFO
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
Insert into tbl_ecam_user
(ecam_user, ecam_dte)
select ui_user, ui_last_login
from User_info
where
ui_last_login = (select max(ui_last_login) from user_info
WHERE ui_last_login IS NOT NULL
AND TO_CHAR(ui_last_login,'DD-MON-YYYY') = 'TRUNC(SYSDATE)'
);
end ecam_trigger;

 

[Dagon]

The problem is that your trigger is against USER_INFO
and you are also trying to select from it. As the table is in the process of being changed by an insert statement, Oracle can't guarantee the consistency of the data, so it throws a mutating table error. This is documented in the Oracle manuals.

You might be better changing the trigger to a statement level one or, since you seem interested in capturing login information, why not use a login trigger ?

 

[amillia]

The login trigger is not an option because the system we are using for a front end is pooling sessions. What do you mean by statement level?

 

[amillia]

I just thought I can make this alot simpler by putting a timestamp into another table when an update occurs to the user_info table. I don't really need the username and date from the user_info. All I need is a table to hold each time a user logs in. That should be simple. I will keep working on it.

 

[Dagon]

A statement level trigger is one in which you miss out the "for each row" option. Consider the following scenario:

insert into table1
select * from table2

If there are 50000 rows in table2, this will generate 50000 inserts. Your trigger will be fired for each of these rows, not just once for the entire insert statement. That is why Oracle has the mutating table restriction - with some many inserts going on, your select may pick up data from rows inserted as part of this transaction and the overall result may be dependant on the order in which the rows are inserted.

A statement level trigger will be fired just once for the entire insert. The rules for these are more relaxed than for row level triggers, so you may be able to do what you want.

 

[Dagon]

I tried it out for you and it works fine as a statement-level trigger:

CREATE OR REPLACE TRIGGER ECAM_TRIGGER
AFTER INSERT
ON USER_INFO
DECLARE
tmpVar NUMBER;
BEGIN
        Insert into tbl_ecam_user
(ecam_user, ecam_dte)
select ui_user, ui_last_login 
  from User_info 
where 
    ui_last_login = (select max(ui_last_login) from user_info 
    WHERE ui_last_login IS NOT NULL
AND TO_CHAR(ui_last_login,'DD-MON-YYYY') = TRUNC(SYSDATE)
);
end ecam_trigger;

Your final condition on the subselect is a bit dodgy as well. You need to remove the single quotes from 'TRUNC(SYSDATE)'. Actually, I would rewrite this as:

AND trunc(ui_last_login) = TRUNC(SYSDATE)

It's not good practice to compare a character string to a date and it won't work if you run it on a system with a diferent NLS_DATE_FORMAT.

 

[amillia]

Thank you, this works great. You made my day.