Got it. After three hair pulling out days it was a school boy error.
Should have had "startpos := colon + 1;" outside the IF statement.
Jeez.
homerjk wrote:
> Hey there,
>
> Can someone please put me out of my misery and tell what the hell im
> doing wrong here!
>
> I have had this trigger working perfectly for the past three months on
> an Oracle 10 database. Recently i wanted to change one part of it but
> when i add in two IF statements the trigger never executes! it just
> processes and processes and never finishes. I had thought that maybe it
> was the new SELECT statement in it that was killing it but if i left it
> in and took out the IF statements its executes instantly as usaul, so
> it is definitely the IF statements that are killing it and i cannot for
> the life of me uderstand why!
>
> Here is the code of the trigger. The parts that have ----- NEW CODE
> written after are the parts ive added in. Without these the trigger
> executes instantly.
>
> CREATE OR REPLACE TRIGGER add_langs
> AFTER UPDATE OF status ON SOURCE
> REFERENCING NEW AS new
> FOR EACH ROW
> DECLARE
> lang VARCHAR2(50);
> numLang INT;
> startpos INT := 1;
> colon INT := 1;
> lastcolon INT := 1;
> exist INT;
>
> BEGIN
> IF :new.status = 'Approved' THEN
> numLang := length(:new.LANGSREQUIRED);
> lastcolon := instr(:new.LANGSREQUIRED, ';', -1);
> WHILE (startpos < lastcolon)
> LOOP
> colon := instr(:new.LANGSREQUIRED, ';', startpos);
> lang := substr(:new.LANGSREQUIRED, startpos, colon - startpos);
>
> SELECT COUNT(sourceid) INTO exist FROM target WHERE
> sourceid=:new.sourceID AND langid=lang; ------ (NEW CODE)
> IF exist = 0 THEN ------ (NEW CODE)
> INSERT INTO TARGET (SourceID, LangID, Status) VALUES
> (:new.sourceID, lang, 'Translation Pending');
> startpos := colon + 1;
> END IF; ------ (NEW CODE)
> END LOOP;
> IF exist = 0 THEN ------ (NEW CODE)
> IF startpos < numLang THEN
> lang := substr(:new.LANGSREQUIRED, startpos);
> INSERT INTO TARGET (SourceID, LangID, Status) VALUES (:new.sourceID,
> lang, 'Translation Pending');
> END IF;
> END IF; ------ (NEW CODE)
> END IF;
> END add_langs;
Received on Thu Jun 22 2006 - 12:12:20 CDT
