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; --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
