Why Doesn’t this Trigger Code Work?

4 03 2010

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 => 'me@myplace.com.au',
    recipients => 'me@myplace.com.au',
    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 => 'me@myplace.com.au',
        RECIPIENTS => 'me@myplace.com.au',
        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.


Actions

Information

3 responses

4 03 2010
jametong

the trigger on operation table can not query/update operation table ..

the only way to go is use autonomous_transaction.. But is this a suitable place for autonomous_transaction?

4 03 2010
Timur Akhmadeev

>What needs to be done to finish fixing this trigger?
Remove all code from the trigger to the job (dbms_job or dbms_scheduler).
Sending an e-mail from a trigger is not the thing to do since the AFTER UPDATE row trigger is subject to multiple calls due to statement restarts.

4 03 2010
Charles Hooper

Jametong and Timur, thanks for leaving dropping by to leave your comments.

The one line that I removed from my cleaned up version of the trigger code before posting it here was:

PRAGMA AUTONOMOUS_TRANSACTION;

What I did not mention, at least not directly, is that the ERP system already had a before update trigger, an after update trigger, and a row level after update trigger on the OPERATION table. At least in Oracle 10.2.0.x it is not possible to control the order in which the triggers fire (two row level triggers on the same table, for instance) so there is always a chance that the STATUS column might be updated in the other row level after the new trigger fired – and that might cause missing emails. The possibility of potentially lost emails was something that I considered before answering the email request.

Timur, your suggestion reminded me of something that I suggested to the author of the email roughly a year ago (but did not think about until I saw your comment) – the event should be logged to another table, and then a scheduled report of some sort (I was thinking of something not scheduled with DBMS_SCHEDULER, but instead a separate program like my Report Server program) to be run against the other table. I believe at that time I suggested the logging approach based on something that I read that was authored by Tom Kyte. If I recall correctly, he was writing about the reasons why you would not write to an operating system file as a means of logging that an event happened – so that if a deadlock happened after that point the text file was written to, or an automatic transaction rollback and restart was preformed (I believe that Tom mentioned this possibility in his most recent book) the log file did not show that the event was successful when it was not, or show that it was successful twice when in fact it only completed once.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: