|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,
ENTRY_DATE DATE DEFAULT SYSDATE,
CREATE SEQUENCE "CCAPADM"."SEQ_PRINT_INDEX"
MAXVALUE 9999999999 MINVALUE 1 INCREMENT BY 1 START WITH 1
CREATE OR REPLACE TRIGGER "CCAPADM".TRG_PRINT_INDEX
AFTER UPDATE OF "PRINTED"
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
SELECT CCAPADM.SEQ_PRINT_INDEX.NEXTVAL INTO SEQ FROM DUAL;
SET PRINT_INDEX = SEQ
WHERE UDMSID = :NEW.UDMSID;
The application produces the following SQL:
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.