CODEWITHSUNDEEP
sqltriggersoracle11g
jjmil03
jjmil03

Reputation: 21

Trigger has mutating error when insert

I am having an issue with a trigger. After I add the trigger to my database, when I try to insert a row that the trigger will act on, I get a mutating table error. I don't know how to avoid this. Would someone mind looking at this and tell me what I am doing wrong, and how to rewrite it?

CREATE OR REPLACE TRIGGER ORNG_INV_LINE_TOTAL_TRIGGER
FOR INSERT OR UPDATE ON ORNG_INV_LINE
COMPOUND TRIGGER 
AFTER EACH ROW IS
BEGIN 
UPDATE ORNG_INVOICE SET Inv_Amount = (SELECT SUM(ORNG_INV_LINE.Inv_Line_Total) 
FROM ORNG_INV_LINE
WHERE ORNG_INVOICE.INV_Num = :NEW.INV_Num);
END AFTER EACH ROW;
END ORNG_INV_LINE_TOTAL_TRIGGER;
/

I'm not sure why it is triggering an error. I'm trying to do the action after the update. All I want to do is get the sum for all of the lines of all matching invoice numbers, and write that value in the INVOICE table. Thanks for the help.

Upvotes: 0

Views: 45

Answers (2)

jjmil03
jjmil03

Reputation: 21

Here is the solution...apparently everyone is busy with Thanksgiving. Hopefully this helps the next guy.

create or replace TRIGGER ORNG_INV_L_TTL_TRIG
AFTER INSERT OR UPDATE OR DELETE ON ORNG_INV_LINE
BEGIN
UPDATE ORNG_INVOICE SET Inv_Amount = (SELECT SUM(Inv_Line_Total) 
FROM ORNG_INV_LINE
WHERE INV_Num = ORNG_INVOICE.INV_Num);
END ORNG_INV_L_TTL_TRIG;
/

Upvotes: 1

Dharmesh Porwal
Dharmesh Porwal

Reputation: 1406

Your trigger is written on the table ORNG_INV_LINE for insert or update and again you made a select on the same table while updating ORNG_INVOICE table so trigger is mutating, to over come this you have to use statement level trigger instead of row level trigger . i.e after each statement should be there in code instead of after each row. This can help you.

Upvotes: 1

Related Questions