-- Certain processes or an ad-hoc delete query can delete master or critical 
-- data causing applications to lose core functionality.  One way to block   
-- such a delete is to create a trigger that throws an application error.
-- The trigger shown below will prevent delete of an actively transacting
-- system (system_cd).

CREATE OR REPLACE TRIGGER trg_block_sys_delete
BEFORE DELETE 
ON  lut_system_codes
FOR EACH ROW

DECLARE
 v_active_sys_cnt   SIMPLE_INTEGER :=0;

BEGIN
 SELECT COUNT(*)  INTO  v_active_sys_cnt
 FROM   lut_system_codes sc
 WHERE  EXISTS
  (SELECT NULL
   FROM   active_trans_systems ats
   WHERE  ats.system_cd = sc.system_cd);

 IF v_active_sys_cnt > 0 THEN
   RAISE_APPLICATION_ERROR(-20000,'System '||:OLD.system_cd||' Is Active); 
 END IF;

END trg_block_sys_delete;
/