[TRIGGER] UPDATE INSERT DELETE 시 TRIGGER 처리

CREATE OR REPLACE TRIGGER ALC_CO_TR
	AFTER INSERT OR UPDATE OR DELETE ON ALC_CO FOR EACH ROW

DECLARE
	REFERENTIAL_INTEGRITY EXCEPTION;
	ORD_CNT NUMBER := 0;

BEGIN
	
	SELECT COUNT(1)
	  INTO ORD_CNT
	  FROM ORD_INFO
	 WHERE ORD_NO = :OLD.ORD_NO
	 ;
	 
	IF ORD_CNT > 0 THEN
		RAISE REFERENTIAL_INTEGRITY;
	END IF;
	
	
	IF INSERTING THEN
		UPDATE ALC_CO_SYNC
		   SET ITEM_STAT_MOD_FG = 'U'
			 , ITEM_STAT_MOD_DTIME = SYSDATE
		 WHERE ITEM_NO = :NEW.ITEM_NO
		 ;
	END IF;
	
	IF DELETING THEN
		UPDATE ALC_CO_SYNC
		   SET ITEM_STAT_MOD_FG = 'D'
		     , ITEM_STAT_MOD_DTIME = SYSDATE
		 WHERE ITEM_NO = :OLD.ITEM_NO	 
	END;
	
	IF UPDATING THEN
		IF :OLD.VENDOR_DISP_YN = :NEW.VENDOR_DISP_YN THEN
			UPDATE ALC_CO_SYNC
			   SET ITEM_STAT_MOD_FG = 'U'
			     , ITEM_STAT_MOD_DTIME = SYSDATE
			 WHERE ITEM_NO = :NEW.ITEM_NO
			;			 
		END IF;	
	END;

/* 예외처리 */
EXCEPTION
	WHEN REFERNTIAL_INTEGRITY THEN
		 RAISE_APPLICATION_ERROR (-2000, '참조값이 존재합니다.');
	
END;