Wednesday 5 November 2008

New trigger for job queue substitution...

Damn that queue kernel coming in after the UBE kernel and updating all of the fields that I update in the "AFTER INSERT" trigger... Do you know what I mean?? My trigger used to update the QUEUE name after the insert from the UBE kernel (insert into the F986110 - Duh?). Anyways so some message must be passed between the queue kernel and the UBE kernel, because the queue kernel just comes in and splats over the top of what my trigger changes... Dirty bastard. Here is the new improved trigger text:

CREATE OR REPLACE TRIGGER JobQueueSubstitution
BEFORE UPDATE
ON SVM812.F986110 FOR EACH ROW
DECLARE
chgFlag NUMBER ;
BEGIN
IF ltrim(rtrim(:new.jcjobque)) = 'QBATCH' and ltrim(rtrim(:new.jcjobsts)) = 'S' and ltrim(rtrim(:old.jcjobsts)) = 'W' THEN
SELECT COUNT(*) INTO chgFlag
FROM F0005
WHERE drsy = '55'
AND drrt = 'JQ'
AND ltrim(rtrim(drky)) = ltrim(rtrim(:NEW.JCUSER)) ;
IF chgFlag > 0 THEN
:new.JCJOBQUE := 'Q812';
END IF;
END IF;
END JobQueueSubstitution;

No comments: