DROP TRIGGER PSIM.RPT_PSIM_TRAITS_AUDIR_TRGR;

CREATE OR REPLACE TRIGGER PSIM.RPT_PSIM_TRAITS_AUDIR_TRGR
after Update or Delete or Insert
on PSIM.RPT_PSIM_TRAITS
REFERENCING NEW AS NEW OLD AS OLD
for each row
declare
    v_person_id  ADR.PERSON.PERSON_ID%TYPE;
    v_cnt        NUMBER := 0;
begin

	IF :NEW.VPID_ID IS NOT NULL THEN

    SELECT COUNT(*)
    INTO   v_cnt
    FROM   ADR.PERSON
    WHERE  VPID_ID = :NEW.VPID_ID;

    IF v_cnt = 0 THEN
      RETURN;
    END IF;

    SELECT PERSON_ID
    INTO   v_person_id
    FROM   ADR.PERSON
    WHERE  VPID_ID = :NEW.VPID_ID;

    SELECT COUNT(*)
    INTO   v_cnt
    FROM   ADR.ENROLLMENT_DETERMINATION
    WHERE  PERSON_ID = v_person_id;

    IF v_cnt = 0 THEN
      RETURN;
    END IF;

		INSERT INTO ADREXT.ADR_EXTRACT_CHGS
		(
			PERSON_ID,
			CHG_DATE,
			ADR_EXTRACT_CHGS_ID
		)
		VALUES
		(
      v_person_id,
			TRUNC(SYSDATE),
			ADREXT.ADR_EXTRACT_CHGS_S.NEXTVAL
		);
	END IF;

end RPT_PSIM_TRAITS_audir_trgr;
/
