Trigger slowing WAY down after adding one IF

From: homerjk <jonathan.keenan_at_gmail.com>
Date: 22 Jun 2006 07:49:40 -0700
Message-ID: <1150987780.042911.78510@c74g2000cwc.googlegroups.com>

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 - 09:49:40 CDT