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