Help with Triggers

Giganews Newsgroups
Subject: Help with Triggers
Posted by:  Al Reid (areid…@reidDASHhome.com)
Date: Tue, 22 Aug 2006

I'm using Oracle 10g R2.

I have an application that seems to occasionally misbehave.  The application should print in the order the records are retrieved
from the database and after printing the "PRINTED" column is updated.  Occasionally, the records print out of order, even though I
am reasonable sure they are processed in the correct order.

To try to troubleshoot the problem I added a column to the table that will hold a number that is retrieved from a sequence.  What I
want to have happen is to have a trigger fire when the PRINTED column is updated and then update the PRINT_INDEX from the sequence.
From that info I hope to be able to be able to determine in what order the records were processed.

I have the following:

CREATE TABLE CCAPADM.JOBLIST (
  UDMSID VARCHAR2(10) PRIMARY KEY,
  USER_ID VARCHAR2(10),
  JOBID VARCHAR2(10),
  DOCUDMSID VARCHAR2(10),
  PO_NUM2 VARCHAR2(6)
  INVOICE_NO VARCHAR2(20),
  JOB_NUM VARCHAR2(6),
  DOCTYPE2 VARCHAR2(25),
  PRINTED VARCHAR2(1),
  ENTRY_DATE DATE  DEFAULT SYSDATE,
  PRINT_INDEX NUMBER(10))

CREATE SEQUENCE "CCAPADM"."SEQ_PRINT_INDEX"
  CYCLE ORDER
  CACHE 5
  MAXVALUE 9999999999 MINVALUE 1 INCREMENT BY 1 START WITH 1

CREATE OR REPLACE TRIGGER "CCAPADM".TRG_PRINT_INDEX
AFTER UPDATE OF "PRINTED"
ON "CCAPADM"."JOBLIST"
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

DECLARE

  SEQ NUMBER(10);

BEGIN

  SELECT CCAPADM.SEQ_PRINT_INDEX.NEXTVAL INTO SEQ FROM DUAL;

  UPDATE CCAPADM.JOBLIST
      SET PRINT_INDEX = SEQ
  WHERE  UDMSID = :NEW.UDMSID;

  COMMIT;

END;

The application produces the following SQL:

UPDATE CCAPADM.JOBLIST
  SET PRINTED = 'Y'
WHERE  UDMSID = '0000000012'

Everything seems to work except that the PRINT_INDEX column does not update.  The PRINTED flag gets updated and the sequence
increments.  If I remove the WHERE clause in the Trigger the PRINT_INDEX column for the entire table IS updated.

Please help.

--
Al Reid

Replies