Error in Trigger

[RamHardikar]

Hi,

I have created a trigger as below

------------------------------------
CREATE OR REPLACE TRIGGER CREATE_INDEX
AFTER INSERT OR DELETE
ON PERSON
FOR EACH ROW

DECLARE
v_id Number;

BEGIN

IF INSERTING = TRUE THEN
update directory_index
set string=substrNew.Last_Name,1,1)
where Index_Id = substrNew.Last_Name,1,1);
ELSIF DELETING = TRUE THEN
Select Id into v_id
from person
where substr(Last_Name,1,1) = substrOld.Last_Name,1,1);

IF (v_id Is NULL) THEN
update directory_index
set string=NULL
where Index_Id = substrOld.Last_Name,1,1);
END IF;
END IF;

END CREATE_INDEX;

-------------------------------

But whenever I run a DELETE SQL on PERSON table I get the following error -

-------------------------------

The following error has occurred:

ORA-04091: table OW_US.PERSON is mutating, trigger/function may not see it
ORA-06512: at "OW_US.CREATE_INDEX", line 11
ORA-04088: error during execution of trigger 'OW_US.CREATE_INDEX'
---------------------------------



Could anyone tell me what is going wrong here.

Thanks,
Ram

 

[LKBrwnDBA]

You cannot issue DML on same table as trigger, this is wrong:

      Select Id into v_id 
        from [red][b]person[/b][/red]
       where substr(Last_Name,1,1) = substr(:Old.Last_Name,1,1);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb