[Oracle] Trigger 연습

트리거 작성 방법

CREATE OR REPLACE TRIGGER [트리거 명]

[AFTER/BEFORE] [INSERT/UPDATE/DELETE] ON [트리거를 적용 시킬 테이블]

FOR EACH ROW 

	BEGIN

	--실행할 내용

	END;

a

 

구독 테이블 INSERT 시 채널 테이블에 구독자 수를 증가시켜주는 트리거

CREATE OR REPLACE TRIGGER INCREASE_SUBSCRIBE_NO
AFTER INSERT ON SUBSCRIBE
FOR EACH ROW
    BEGIN
        UPDATE CHANNEL SET SUBSCRIBE_NO = SUBSCRIBE_NO + 1
        WHERE CHANNEL_ID = :NEW.CHANNEL_ID;
    END;

구독 테이블 DELETE 시 채널 테이블에 구독자 수를 감소시켜주는 트리거

CREATE OR REPLACE TRIGGER DECREASE_SUBSCRIBE_NO
AFTER DELETE ON SUBSCRIBE
FOR EACH ROW
    BEGIN
        UPDATE CHANNEL SET SUBSCRIBE_NO = SUBSCRIBE_NO - 1
        WHERE CHANNEL_ID = :OLD.CHANNEL_ID;
    END;

INSERT 때는 NEW를 쓰지만 DELETE 때는 이전 값에 대한 참조이므로 OLD를 사용한다. 

 

 

연습용 ERD

연습용 테이블 스키마

Drop table system.channel;
Drop table system.content;
Drop table system.reply;
Drop table system.subscribe;
Drop table system.youtube_user;

CREATE TABLE system.channel (
    channel_id   NUMBER NOT NULL,
    channel_name VARCHAR2(20 BYTE) NOT NULL,
    subscribe_no NUMBER NOT NULL,
    content_no   NUMBER NOT NULL,
    description  VARCHAR2(200 BYTE)
);

        
CREATE TABLE system.content (
    content_id    NUMBER NOT NULL,
    content_title VARCHAR2(200 BYTE) NOT NULL,
    content_url   VARCHAR2(200 BYTE) NOT NULL,
    channel_id    NUMBER NOT NULL,
    created_at     DATE NOT NULL
);


CREATE TABLE system.reply (
    reply_id   NUMBER NOT NULL,
    content_id NUMBER NOT NULL,
    user_id    VARCHAR2(20 BYTE) NOT NULL,
    reple      VARCHAR2(200 BYTE) NOT NULL,
    created_at  DATE NOT NULL
);

CREATE TABLE system.subscribe (
    subscribe_id    NUMBER NOT NULL,
    user_id         VARCHAR2(20 BYTE) NOT NULL,
    channel_id      NUMBER NOT NULL
);

CREATE TABLE system.youtube_user (
    user_id   VARCHAR2(20 BYTE) NOT NULL,
    user_pw   VARCHAR2(20 BYTE) NOT NULL,
    user_name VARCHAR2(20 BYTE) NOT NULL
);


ALTER TABLE system.youtube_user
    ADD CONSTRAINT youtube_user_pk PRIMARY KEY ( user_id );

ALTER TABLE system.channel
    ADD CONSTRAINT channel_pk PRIMARY KEY ( channel_id );


ALTER TABLE system.subscribe
    ADD CONSTRAINT subscribe_pk PRIMARY KEY ( subscribe_id )
        USING INDEX system.subscribe_pk;

ALTER TABLE system.subscribe
    ADD CONSTRAINT subscribe_channel_fk FOREIGN KEY ( channel_id )
        REFERENCES system.channel ( channel_id )
    NOT DEFERRABLE;

ALTER TABLE system.subscribe
    ADD CONSTRAINT subscribe_youtube_user_fk FOREIGN KEY ( user_id )
        REFERENCES system.youtube_user ( user_id )
    NOT DEFERRABLE;


ALTER TABLE system.content
    ADD CONSTRAINT content_pk PRIMARY KEY ( content_id );

ALTER TABLE system.content
    ADD CONSTRAINT content_channel_fk FOREIGN KEY ( channel_id )
        REFERENCES system.channel ( channel_id );

ALTER TABLE system.reply
    ADD CONSTRAINT reply_pk PRIMARY KEY ( reply_id );

ALTER TABLE system.reply
    ADD CONSTRAINT reply_content_fk FOREIGN KEY ( content_id )
        REFERENCES system.content ( content_id );

ALTER TABLE system.reply
    ADD CONSTRAINT reply_youtube_user_fk FOREIGN KEY ( user_id )
        REFERENCES system.youtube_user ( user_id );

 

테스트 데이터 

-- USER DATA
INSERT INTO YOUTUBE_USER
VALUES ('aaa123', 'password1', '김철수');

INSERT INTO YOUTUBE_USER
VALUES ('bbb123', 'password2', '김영희');

INSERT INTO YOUTUBE_USER
VALUES ('ccc123', 'password3', '홍길동');

INSERT INTO YOUTUBE_USER
VALUES ('ddd123', 'password4', '이순신');

INSERT INTO YOUTUBE_USER
VALUES ('eee123', 'password5', '김유신');


-- CHANNEL DATA
INSERT INTO CHANNEL
VALUES(1, '철수 채널', 0, 0, '안녕하세요 철수 채널입니다.');

INSERT INTO CHANNEL
VALUES(2, '영희 채널', 0, 0, '안녕하세요 영희 채널입니다.');

INSERT INTO CHANNEL
VALUES(3, '길동 채널', 0, 0, '안녕하세요 길동 채널입니다.');

INSERT INTO CHANNEL
VALUES(4, '순신 채널', 0, 0, '안녕하세요 순신 채널입니다.');

INSERT INTO CHANNEL
VALUES(5, '유신 채널', 0, 0, '안녕하세요 유신 채널입니다.');

--CONTENT TRIGGER 
CREATE OR REPLACE TRIGGER INCREASE_CONTENT_NO
AFTER INSERT ON CONTENT
FOR EACH ROW
    BEGIN
        UPDATE CHANNEL SET CONTENT_NO = CONTENT_NO + 1
        WHERE CHANNEL_ID = :NEW.CHANNEL_ID;
    END;

CREATE OR REPLACE TRIGGER DECREASE_CONTENT_NO
AFTER DELETE ON CONTENT
FOR EACH ROW
    BEGIN
        UPDATE CHANNEL SET CONTENT_NO = CONTENT_NO - 1
        WHERE CHANNEL_ID = :OLD.CHANNEL_ID;
    END;

-- CONTENT DATA
INSERT INTO CONTENT
VALUES(1, '''카터'' 칼로 깎은 듯한 어깨...(Feat. 영화 ''카터''의 주원)', 'https://www.youtube.com/watch?v=9wwMJn-X2ME', 1, to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

INSERT INTO CONTENT
VALUES(2, '몰디브에서 운동 한 잔..', 'https://www.youtube.com/watch?v=e4WnrhXTjg8', 2, to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

INSERT INTO CONTENT
VALUES(3, '가수 맞으시죠...? (Feat. 지피티)', 'https://www.youtube.com/watch?v=gGeBtQ7hmQ4', 3, to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

INSERT INTO CONTENT
VALUES(4, '이 사람이다... (Feat. 김계란, 칼로바이)', 'https://www.youtube.com/watch?v=zbBKX7I4Ef0', 4, to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

INSERT INTO CONTENT
VALUES(5, '[매미킴 다큐] 격투기 선수로 산다는 것 l 윤창민 원챔피언십 태국 원정기', 'https://www.youtube.com/watch?v=fYgZo3kcIIs', 5, to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));


-- REPLY DATA
INSERT INTO REPLY
VALUES(1, 1, 'aaa123', '종국이형 사심 방송ㅋㅋㅋㅋ 즐거워 하시는거 보는데 나도 모르게 미소 지고있었음ㅋ', to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

INSERT INTO REPLY
VALUES(2, 2, 'bbb123', 'I worked out earlier today but seeing Kim Jong Kook’s obvious enthusiasm and happiness in the gym almost makes me want to do more…get well soon!!!', to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

INSERT INTO REPLY
VALUES(3, 3, 'ccc123', '가끔씩 운동하기 귀찮아질때 종국형님 영상보고 초심으로 돌아가서 열심히 운동할수 있어 너무 감사합니다 진짜 제대로 된 운동법을 배우고 갑니다', to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

INSERT INTO REPLY
VALUES(4, 4, 'ddd123', '코치님 표정이 자부심이 있어 보이셔서 너무 보기 좋네요 저런 지도자 밑에서 배우니까 학생들도 밝고 착해보입니다 응원합니다', to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

INSERT INTO REPLY
VALUES(5, 5, 'eee123', '우와... 피지컬... 진짜... 축복받으셨네요... 다른 운동 하셨어도 대성하셨을듯...', to_date(sysdate,'yyyy.mm.dd hh24:mi:ss'));

-- SUBSCRIBE TRIGER
CREATE OR REPLACE TRIGGER INCREASE_SUBSCRIBE_NO
AFTER INSERT ON SUBSCRIBE
FOR EACH ROW
    BEGIN
        UPDATE CHANNEL SET SUBSCRIBE_NO = SUBSCRIBE_NO + 1
        WHERE CHANNEL_ID = :NEW.CHANNEL_ID;
    END;

CREATE OR REPLACE TRIGGER DECREASE_SUBSCRIBE_NO
AFTER DELETE ON SUBSCRIBE
FOR EACH ROW
    BEGIN
        UPDATE CHANNEL SET SUBSCRIBE_NO = SUBSCRIBE_NO - 1
        WHERE CHANNEL_ID = :OLD.CHANNEL_ID;
    END;

-- SUBSCRIBE DATA
INSERT INTO SUBSCRIBE
VALUES(1, 'aaa123', 5);

INSERT INTO SUBSCRIBE
VALUES(2, 'bbb123', 4);

INSERT INTO SUBSCRIBE
VALUES(3, 'eee123', 3);

INSERT INTO SUBSCRIBE
VALUES(4, 'ccc123', 2);

INSERT INTO SUBSCRIBE
VALUES(5, 'ddd123', 1);


-- TEST TABLE CHECK
SELECT * FROM youtube_user;
SELECT * FROM CHANNEL;
SELECT * FROM CONTENT;
SELECT * FROM REPLY;
SELECT * FROM SUBSCRIBE;

-- TEST TRIGGER CHECK
SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS;