Untitled

CREATE OR REPLACE TRIGGER SVAS.trg_isl_region_cd
AFTER INSERT OR UPDATE ON issuer_location
FOR EACH ROW
BEGIN
   IF (:new.region_cd IS NOT NULL AND
       :new.country_id IS NOT NULL AND 
       (INSERTING OR (:new.region_cd != :old.region_cd) OR (:new.country_id != :old.country_id))) THEN
       
       -- Call the package procedure to check referential integrity
       pkg_lkp_regions_tbl.check_ri(
           p_country_id => :new.country_id,
           p_region_cd  => :new.region_cd
       );

   END IF;
END;
/


ALTER TABLE issuer_location 
ADD CONSTRAINT fky_isl_region_cd 
FOREIGN KEY (country_id, region_cd) 
REFERENCES syn_regions_tbl (country_id, region_cd);