/* * JsTree 추적 로그 테이블 * 트리거 Log를 저장합니다. */ CREATE TABLE T_JSTREE_COMPARE_INFO_LOG ( C_ID NUMBER NOT NULL, C_PARENTID NUMBER NOT NULL, C_POSITION NUMBER NOT NULL, C_LEFT NUMBER NOT NULL, C_RIGHT NUMBER NOT NULL, C_LEVEL NUMBER NOT NULL, C_TITLE VARCHAR2(4000 BYTE), C_TYPE VARCHAR2(4000 BYTE), C_METHOD VARCHAR2(4000 BYTE), C_STATE VARCHAR2(4000 BYTE), C_DATE DATE NOT NULL ); COMMENT ON TABLE T_JSTREE_COMPARE_INFO_LOG IS '기본 트리 스키마 트리거 로그'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_ID IS '노드 아이디'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_PARENTID IS '부모 노드 아이디'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_POSITION IS '노드 포지션'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_LEFT IS '노드 좌측 끝 포인트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_RIGHT IS '노드 우측 끝 포인트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_LEVEL IS '노드 DEPTH '; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_TITLE IS '노드 명'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_TYPE IS '노드 타입'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_METHOD IS '노드 변경 행위'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_STATE IS '노드 상태값 ( 이전인지. 이후인지)'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_DATE IS '노드 변경 시'; /* * JsTree */ CREATE TABLE T_JSTREE_COMPARE_INFO ( C_ID NUMBER NOT NULL, C_PARENTID NUMBER NOT NULL, C_POSITION NUMBER NOT NULL, C_LEFT NUMBER NOT NULL, C_RIGHT NUMBER NOT NULL, C_LEVEL NUMBER NOT NULL, C_TITLE VARCHAR2(4000 BYTE), C_TYPE VARCHAR2(4000 BYTE), CAL_TIME VARCHAR2(4000 BYTE), TOP_IMAGE_URL_1 VARCHAR2(4000 BYTE), TOP_IMAGE_URL_2 VARCHAR2(4000 BYTE), TOP_IMAGE_URL_3 VARCHAR2(4000 BYTE), TOP_VENDER_URL VARCHAR2(4000 BYTE), TOP_VENDER_TEXT VARCHAR2(4000 BYTE), TOP_POST1_ICON VARCHAR2(4000 BYTE), TOP_POST1_URL VARCHAR2(4000 BYTE), TOP_POST1_TEXT VARCHAR2(4000 BYTE), TOP_POST2_ICON VARCHAR2(4000 BYTE), TOP_POST2_URL VARCHAR2(4000 BYTE), TOP_POST2_TEXT VARCHAR2(4000 BYTE), TOP_POST3_ICON VARCHAR2(4000 BYTE), TOP_POST3_URL VARCHAR2(4000 BYTE), TOP_POST3_TEXT VARCHAR2(4000 BYTE), TOP_POST4_ICON VARCHAR2(4000 BYTE), TOP_POST4_URL VARCHAR2(4000 BYTE), TOP_POST4_TEXT VARCHAR2(4000 BYTE), TOP_POST5_ICON VARCHAR2(4000 BYTE), TOP_POST5_URL VARCHAR2(4000 BYTE), TOP_POST5_TEXT VARCHAR2(4000 BYTE), MID_IMAGE_URL_1 VARCHAR2(4000 BYTE), MID_IMAGE_URL_2 VARCHAR2(4000 BYTE), MID_IMAGE_URL_3 VARCHAR2(4000 BYTE), MID_VENDER_URL VARCHAR2(4000 BYTE), MID_VENDER_TEXT VARCHAR2(4000 BYTE), MID_POST1_ICON VARCHAR2(4000 BYTE), MID_POST1_URL VARCHAR2(4000 BYTE), MID_POST1_TEXT VARCHAR2(4000 BYTE), MID_POST2_ICON VARCHAR2(4000 BYTE), MID_POST2_URL VARCHAR2(4000 BYTE), MID_POST2_TEXT VARCHAR2(4000 BYTE), MID_POST3_ICON VARCHAR2(4000 BYTE), MID_POST3_URL VARCHAR2(4000 BYTE), MID_POST3_TEXT VARCHAR2(4000 BYTE), MID_POST4_ICON VARCHAR2(4000 BYTE), MID_POST4_URL VARCHAR2(4000 BYTE), MID_POST4_TEXT VARCHAR2(4000 BYTE), MID_POST5_ICON VARCHAR2(4000 BYTE), MID_POST5_URL VARCHAR2(4000 BYTE), MID_POST5_TEXT VARCHAR2(4000 BYTE), BOT_IMAGE_URL_1 VARCHAR2(4000 BYTE), BOT_IMAGE_URL_2 VARCHAR2(4000 BYTE), BOT_IMAGE_URL_3 VARCHAR2(4000 BYTE), BOT_VENDER_URL VARCHAR2(4000 BYTE), BOT_VENDER_TEXT VARCHAR2(4000 BYTE), BOT_POST1_ICON VARCHAR2(4000 BYTE), BOT_POST1_URL VARCHAR2(4000 BYTE), BOT_POST1_TEXT VARCHAR2(4000 BYTE), BOT_POST2_ICON VARCHAR2(4000 BYTE), BOT_POST2_URL VARCHAR2(4000 BYTE), BOT_POST2_TEXT VARCHAR2(4000 BYTE), BOT_POST3_ICON VARCHAR2(4000 BYTE), BOT_POST3_URL VARCHAR2(4000 BYTE), BOT_POST3_TEXT VARCHAR2(4000 BYTE), BOT_POST4_ICON VARCHAR2(4000 BYTE), BOT_POST4_URL VARCHAR2(4000 BYTE), BOT_POST4_TEXT VARCHAR2(4000 BYTE), BOT_POST5_ICON VARCHAR2(4000 BYTE), BOT_POST5_URL VARCHAR2(4000 BYTE), BOT_POST5_TEXT VARCHAR2(4000 BYTE), CONSTRAINT T_JSTREE_COMPARE_INFO_PK PRIMARY KEY (C_ID) /* * CONSTRAINT T_JSTREE_COMPARE_INFO_FK1 FOREIGN KEY (OTHER_ID) REFERENCES OTHER T_JSTREE_COMPARE_INFO(C_ID) ON DELETE CASCADE */ ); COMMENT ON TABLE T_JSTREE_COMPARE_INFO IS '기본 트리 스키마'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_ID IS '노드 아이디'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_PARENTID IS '부모 노드 아이디'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_POSITION IS '노드 포지션'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_LEFT IS '노드 좌측 끝 포인트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_RIGHT IS '노드 우측 끝 포인트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_LEVEL IS '노드 DEPTH '; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_TITLE IS '노드 명'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_TYPE IS '노드 타입'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.CAL_TIME IS '누적 집계 시간'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_IMAGE_URL_1 IS '상위 대표 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_IMAGE_URL_2 IS '상위 서브 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_IMAGE_URL_3 IS '상위 테일 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_VENDER_URL IS '상위 벤더 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_VENDER_TEXT IS '상위 벤더 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST1_ICON IS '상위 포스트 1번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST1_URL IS '상위 포스트 1번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST1_TEXT IS '상위 포스트 1번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST2_ICON IS '상위 포스트 2번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST2_URL IS '상위 포스트 2번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST2_TEXT IS '상위 포스트 2번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST3_ICON IS '상위 포스트 3번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST3_URL IS '상위 포스트 3번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST3_TEXT IS '상위 포스트 3번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST4_ICON IS '상위 포스트 4번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST4_URL IS '상위 포스트 4번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST4_TEXT IS '상위 포스트 4번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST5_ICON IS '상위 포스트 5번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST5_URL IS '상위 포스트 5번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST5_TEXT IS '상위 포스트 5번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_IMAGE_URL_1 IS '중위 대표 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_IMAGE_URL_2 IS '중위 서브 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_IMAGE_URL_3 IS '중위 테일 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_VENDER_URL IS '중위 벤더 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_VENDER_TEXT IS '중위 벤더 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST1_ICON IS '중위 포스트 1번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST1_URL IS '중위 포스트 1번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST1_TEXT IS '중위 포스트 1번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST2_ICON IS '중위 포스트 2번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST2_URL IS '중위 포스트 2번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST2_TEXT IS '중위 포스트 2번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST3_ICON IS '중위 포스트 3번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST3_URL IS '중위 포스트 3번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST3_TEXT IS '중위 포스트 3번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST4_ICON IS '중위 포스트 4번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST4_URL IS '중위 포스트 4번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST4_TEXT IS '중위 포스트 4번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST5_ICON IS '중위 포스트 5번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST5_URL IS '중위 포스트 5번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST5_TEXT IS '중위 포스트 5번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_IMAGE_URL_1 IS '하위 대표 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_IMAGE_URL_2 IS '하위 서브 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_IMAGE_URL_3 IS '하위 테일 이미지'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_VENDER_URL IS '하위 벤더 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_VENDER_TEXT IS '하위 벤더 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST1_ICON IS '하위 포스트 1번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST1_URL IS '하위 포스트 1번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST1_TEXT IS '하위 포스트 1번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST2_ICON IS '하위 포스트 2번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST2_URL IS '하위 포스트 2번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST2_TEXT IS '하위 포스트 2번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST3_ICON IS '하위 포스트 3번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST3_URL IS '하위 포스트 3번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST3_TEXT IS '하위 포스트 3번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST4_ICON IS '하위 포스트 4번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST4_URL IS '하위 포스트 4번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST4_TEXT IS '하위 포스트 4번 링크 텍스트'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST5_ICON IS '하위 포스트 5번 아이콘'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST5_URL IS '하위 포스트 5번 링크'; COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST5_TEXT IS '하위 포스트 5번 링크 텍스트'; /* * 인덱스는 되도록 걸지 말것. * CREATE UNIQUE INDEX I_COMPREHENSIVETREE ON T_JSTREE_COMPARE_INFO * ("C_ID" ASC) * DROP SEQUENCE S_JSTREE_COMPARE_INFO; */ CREATE SEQUENCE S_JSTREE_COMPARE_INFO START WITH 10 MAXVALUE 999999999999999999999999999 MINVALUE 0 NOCYCLE CACHE 20 NOORDER; /* * JsTree 트리거 */ CREATE OR REPLACE TRIGGER "TG_JSTREE_COMPARE_INFO" BEFORE DELETE OR INSERT OR UPDATE ON T_JSTREE_COMPARE_INFO REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE tmpVar NUMBER; /****************************************************************************** NAME: TRIGGER_COMPREHENSIVETREE PURPOSE: REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2012-08-29 1. Created this trigger. NOTES: Automatically available Auto Replace Keywords: Object Name: TRIGGER_COMPREHENSIVETREE Sysdate: 2012-08-29 Date and Time: 2012-08-29, 오후 5:26:44, and 2012-08-29 오후 5:26:44 Username: (set in TOAD Options, Proc Templates) Table Name: T_JSTREE_COMPARE_INFO (set in the "New PL/SQL Object" dialog) Trigger Options: (set in the "New PL/SQL Object" dialog) ******************************************************************************/ BEGIN tmpVar := 0; IF UPDATING THEN insert into T_JSTREE_COMPARE_INFO_LOG values (:old.C_ID,:old.C_PARENTID,:old.C_POSITION,:old.C_LEFT,:old.C_RIGHT,:old.C_LEVEL,:old.C_TITLE,:old.C_TYPE,'update','변경이전데이터',sysdate); insert into T_JSTREE_COMPARE_INFO_LOG values (:new.C_ID,:new.C_PARENTID,:new.C_POSITION,:new.C_LEFT,:new.C_RIGHT,:new.C_LEVEL,:new.C_TITLE,:new.C_TYPE,'update','변경이후데이터',sysdate); END IF; IF DELETING THEN insert into T_JSTREE_COMPARE_INFO_LOG values (:old.C_ID,:old.C_PARENTID,:old.C_POSITION,:old.C_LEFT,:old.C_RIGHT,:old.C_LEVEL,:old.C_TITLE,:old.C_TYPE,'delete','삭제된데이터',sysdate); END IF; IF INSERTING THEN insert into T_JSTREE_COMPARE_INFO_LOG values (:new.C_ID,:new.C_PARENTID,:new.C_POSITION,:new.C_LEFT,:new.C_RIGHT,:new.C_LEVEL,:new.C_TITLE,:new.C_TYPE,'insert','삽입된데이터',sysdate); END IF; EXCEPTION WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END TG_JSTREE_COMPARE_INFO; /** PK - FK **/ ALTER TABLE T_JSTREE_MENU ADD( CONSTRAINT T_JSTREE_MENU_FK_CI FOREIGN KEY (COMPARE_INFO_ID) REFERENCES T_JSTREE_COMPARE_INFO (C_ID)); /