create table sync_mng_external (
        target_cs       character(8)                    not null,
	last_mod_time   timestamp without time zone     not null,
	arearp_cs       character(8)                    not null,
	zonerp_cs       character(8)                    not null);

COMMENT ON TABLE sync_mng_external IS 'Sync_Terminal management table (external routing data)';
COMMENT ON COLUMN sync_mng_external.target_cs IS 'target call sign';COMMENT ON COLUMN sync_mng_external.last_mod_time IS 'last modify date for sync';
COMMENT ON COLUMN sync_mng_external.arearp_cs IS 'area repetar call sign';
COMMENT ON COLUMN sync_mng_external.zonerp_cs IS 'zone repetar call sign';

ALTER TABLE ONLY sync_mng_external
    ADD CONSTRAINT pk_sync_mng_external PRIMARY KEY (target_cs);CREATE PROCEDURAL LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION process_sync_mng_external() RETURNS TRIGGER AS $$
    DECLARE
        ext_data  sync_mng_external%ROWTYPE;

    BEGIN
        SELECT * INTO ext_data FROM sync_mng_external WHERE target_cs = NEW.target_cs;
        IF NOT FOUND THEN
               RETURN NEW;
        END IF;

        IF ( NEW.last_mod_time < ext_data.last_mod_time ) AND NOT ( ext_data.zonerp_cs = 'NOCALL99' ) THEN
                NEW.arearp_cs     := ext_data.arearp_cs;
                NEW.zonerp_cs     := ext_data.zonerp_cs;
        END IF;
        RETURN NEW;
    END;$$

LANGUAGE plpgsql;
CREATE TRIGGER trigger_sync_mng_ext
 BEFORE INSERT OR UPDATE ON sync_mng
 FOR EACH ROW EXECUTE PROCEDURE process_sync_mng_external();