March 4, 2010
A couple of days ago I received an interesting email from an ERP mailing list. That email contained an Oracle trigger that looked like it would be helpful, but the author of the trigger was struggling a bit with the PL/SQL code and asked for some assistance with the trigger code. The trigger code contained in the email looked like this:
CREATE OR REPLACE TRIGGER "TESTUSER"."TEST3" AFTER UPDATE OF "STATUS" ON "TESTUSER"."OPERATION" REFERENCING OLD AS OLDDATA NEW AS NEWDATA FOR EACH ROW DECLARE msg1 varchar2(20000) := 'Despatch as been closed for '; msg2 varchar2(20000) := ' And there are operations not closed'; recipients varchar2(20000); CountPlease number; Customer varchar2(20000); Base varchar2(20000) := :newdata.workorder_base_id; BEGIN IF UPDATING THEN if :newdata.resource_id = 'DESPATCH' and :Olddata.status <> :newdata.status and :newdata.status = 'C' then select name into Customer from customer where id in (select customer_id from customer_order where id = Base); ---jaggie bit here select count(distinct(workorder_lot_id||sequence_no||resource_id)) into CountPlease from operation where status = 'R' and workorder_type = 'W' and workorder_base_id = Base and not resource_id in ('DEVELOPMENT','START','DESPATCH','SERVICE'); ---jaggie bit ends. ---Not yet inserted If CountPlease greater than 0 then... UTL_MAIL.SEND ( sender => 'email@example.com', recipients => 'firstname.lastname@example.org', subject => msg1|| Base, message => msg1|| Customer|| ' , ' || Base || msg2||CountPlease); end if; END IF; END; /
Paraphrasing the description of the problem:
I am having a problem with a trigger that I am trying to add to the OPERATION table. The purpose of the trigger is to send an email if someone generates a labor ticket that causes the STATUS column of a row in the OPERATION table to change from ‘R’ (released) to ‘C’ (closed) when the RESOURCE_ID column contains the word ‘DESPATCH’, and there are other associated rows (excluding the rows with the RESOURCE_ID set to ‘DEVELOPMENT’, ‘START’, ‘DESPATCH’, or ‘SERVICE’) in that table associated with the modified row where the STATUS column is set to ‘R’.
The trigger works up to the point of the SQL statement containing the COUNT function, which is ultimately used to determine if the email should be sent. The trigger compiles without error, however when a labor ticket is created (a row is inserted into the LABOR_TICKET table) that causes the STATUS of the corresponding row in the OPERATION table to change from ‘R’ to ‘C’ (caused by a trigger on the LABOR_TICKET table that then causes another trigger to fire on the OPERATION table), I receive the dreaded error in the ERP program of “invalid handle”. Yet my SQL seems sound via Sqlplus.
My other select statements work, and my email generates, provided I leave out the SELECT COUNT… SQL statement that is used to see if any other related rows in the OPERATION table have a status of ‘R’.
PL/SQL is not my native language (I think that it is still English), so I started by cleaning up the trigger code into a more readable form:
CREATE OR REPLACE TRIGGER "TESTUSER"."TEST3" AFTER UPDATE OF "STATUS" ON "TESTUSER"."OPERATION" REFERENCING OLD AS OLDDATA NEW AS NEWDATA FOR EACH ROW DECLARE MSG1 VARCHAR2(500) := 'Despatch as been closed for '; MSG2 VARCHAR2(500) := ' And there are operations not closed '; RECIPIENTS VARCHAR2(500); COUNTPLEASE NUMBER; CUSTOMERNAME VARCHAR2(50); BASEID VARCHAR2(30) := :NEWDATA.WORKORDER_BASE_ID; BEGIN IF UPDATING THEN IF :NEWDATA.RESOURCE_ID = 'DESPATCH' AND :OLDDATA.STATUS <> :NEWDATA.STATUS AND :NEWDATA.STATUS = 'C' THEN SELECT C.NAME INTO CUSTOMERNAME FROM CUSTOMER C, CUSTOMER_ORDER CO WHERE C.ID = CO.CUSTOMER_ID AND CO.ID = BASEID; ---jaggie bit here SELECT COUNT(SEQUENCE_NO||RESOURCE_ID) INTO COUNTPLEASE FROM OPERATION WHERE STATUS = 'R' AND WORKORDER_TYPE = 'W' AND WORKORDER_BASE_ID = :NEWDATA.WORKORDER_BASE_ID AND WORKORDER_LOT_ID = :NEWDATA.WORKORDER_LOT_ID AND WORKORDER_SPLIT_ID='0' AND RESOURCE_ID NOT IN ('DEVELOPMENT','START','DESPATCH','SERVICE'); ---jaggie bit ends. IF COUNTPLEASE > 0 THEN UTL_MAIL.SEND ( SENDER => 'email@example.com', RECIPIENTS => 'firstname.lastname@example.org', SUBJECT => MSG1|| BASEID, MESSAGE => MSG1|| CUSTOMERNAME|| ' , ' || BASEID || MSG2 ||COUNTPLEASE); NULL; END IF; END IF; END IF; END; /
After the clean up I managed to compile the trigger and then performed a test with the ERP program. The “dreaded error message” looked something like this:
ORA-04091: table TESTUSER.OPERATION is mutating, trigger/function may not see it ORA-06512: at “TESTUSER.TEST3”, line 24 ORA-04088: error during execution of trigger ‘TESTUSER.TEST3’ ORA-06512: at “TESTUSER.AIUD_LABTICK_STMT”, line 137 ORA-06512: at “TESTUSER.TAIUD
Something is wrong – the table to which the trigger is attached is mutating, which apparently means that it is changing into something other than a table (a chair, maybe?). 🙂
What needs to be done to finish fixing this trigger? If you are struggling to find the answer, I might suggest taking a look at page 287 of the book “Beginning PL/SQL From Novice to Professional” (I am not saying that the answer is on that page, but you might want to look at it just for fun). The documentation also suggests another approach for dealing with mutating tables, but I do not know if it will help in this case. So, what needs to be done – removing NULL; was not the solution.