ora-04091,ORA-06512,ORA-04088 mutating ?

[oraprog]

I create this trigger:
CREATE OR REPLACE TRIGGER T_HST AFTER INSERT
On HST
FOR EACH ROW
DECLARE
Begin
INSERT INTO HST (EMP_NO, START_DATE )
SELECT EMP_NO, SYSDATE
FROM HST
WHERE EMP_NO = :NEW.EMP_NO;
End T_HRS_SAL_PERIOD_HSTY_UPD_PRI;
/


when I insert a new record in table HST I get this error:

ORA-04091: table HST is mutating, trigger/function may not see it
ORA-06512: at "T_HST", line 3
ORA-04088: error during execution of trigger 'T_HST'


What I wrong in this trigger?

Thanks

 

[Dagon]

This is pretty much exactly what you're NOT supposed to do with triggers. What are you trying to achieve exactly? Are you really trying to create a duplicate row in the HST table or do you mistakenly think that the AFTER INSERT trigger replaces the insert that the caused the trigger to fire in the first place?

I would suggest spending a couple of hours reading the documentation about triggers and how they work.

For Oracle-related work, contact me through Linked-In.

 

[stefanhei]

To answer the question what's wrong with the trigger:
Each time you insert a row into table HST your trigger would insert another row which would cause the trigger to fire again producing an endless recursion.
That is one reason why within the trigger you can't read or write the table a row-level-trigger is fired from.
I second Dagons suggestion that you read the documentation about triggers and rethink what you are trying to archive.

 

[Beilstwh]

and if all you are trying to do is set start_date to sysdate then

CREATE OR REPLACE TRIGGER T_HST before INSERT
On HST
FOR EACH ROW
Begin
:new.start_date := sysdate;
End T_HRS_SAL_PERIOD_HSTY_UPD_PRI;
/

Bill
Lead Application Developer
New York State, USA