what do I need a loop for when I there is only ever one record returned per SELECT?
On 16 Sep, 14:21, sonty <saurabh.zen...@gmail.com> wrote: > Are we missing something > > yes may be the loop > > -Sonty > > On Sep 16, 4:13 pm, Chris <christopherc...@hotmail.com> wrote: > > > > > I think I have managed a way of doing it which is a bit crude I > > think. If anyone can think of a better way of doing it, please > > advise: > > > CREATE OR REPLACE TRIGGER check_bom_exists_trig > > BEFORE INSERT OR UPDATE ON XXMEL_SPARES_BOM_LOOKUP > > FOR EACH ROW > > DECLARE > > -- bom must exist in msib before it can be inserted > > new_target VARCHAR2(50); > > new_source VARCHAR2(50); > > > CURSOR check_target_cur IS > > SELECT msib.segment1 > > FROM mtl_system_items_b msib > > WHERE msib.segment1 = :NEW.target_bom; > > > CURSOR check_source_cur IS > > SELECT msib.segment1 > > FROM mtl_system_items_b msib > > WHERE msib.segment1 = :NEW.source_bom; > > > BEGIN > > > OPEN check_target_cur; > > FETCH check_target_cur INTO new_source; > > IF (check_target_cur%NOTFOUND) THEN > > RAISE_APPLICATION_ERROR(-20012, 'The target bom does not exist in > > oracle!'); > > ELSE > > CLOSE check_target_cur; > > END IF; > > > OPEN check_source_cur; > > FETCH check_source_cur INTO new_source; > > IF (check_source_cur%NOTFOUND) THEN > > RAISE_APPLICATION_ERROR(-20012, 'The source bom does not exist in > > oracle!'); > > ELSE > > CLOSE check_source_cur; > > END IF; > > > END;- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
