Database

(23.01.10)Oracle ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค : on update cascade ๊ธฐ๋Šฅ trigger๋กœ ๊ตฌํ˜„ํ•˜

ํ”„๋กœ๊ทธ๋ž˜๋จธ ์˜ค์›” 2023. 1. 11.

โ—โ—on update cascade ๊ธฐ๋Šฅ trigger๋กœ ๊ตฌํ˜„ํ•˜๊ธฐโ—โ—

 

 

 

์˜ค๋ผํด๊ณผ MS-SQL์€ FK ์ œ์•ฝ์„ ๊ฑธ๋•Œ ON DELETE CASCADE ๋ฅผ ์ง€์›ํ•˜์ง€๋งŒ, ON UPDATE CASCADE๋Š” MS-SQL ๋งŒ ์ง€์›ํ•œ๋‹ค.

 

์˜ค๋ผํด์—์„œ๋„ ON UPDATE CASCADE๊ฐ€ ๋งŽ์ด ํ•„์š” ํ•˜๊ธฐ์— ์กฐ๊ธˆ ๋ถˆํŽธํ•˜์ง€๋งŒ Trigger๋ฅผ ์ด์šฉํ•ด ๊ตฌํ˜„ํ•˜๋Š” ๋ฐฉ๋ฒ•์ด ์žˆ๋‹ค.

 

 

ํŠธ๋ฆฌ๊ฑฐ ํŠน์ง•

- AFTER, BEFORE๋ฅผ ํ†ตํ•ด TRIGGER๊ฐ€ ์–ธ์ œ ์‹คํ–‰๋ ์ง€ ๊ฒฐ์ •

- FOR EACH ROW, ์ฆ‰ ๋ชจ๋“  ํ…Œ์ด๋ธ”์„ ๋ฐ˜๋ณตํ•˜๋ฉด์„œ ์‹คํ–‰๋œ๋‹ค

- BEGIN๊ณผ END ์‚ฌ์ด์— SQL๋ฌธ ์‚ฝ์ž… ๊ฐ€๋Šฅ, ์ด๋•Œ ๋ฐ˜๋“œ์‹œ ; ๋กœ ๋์„ ๋‚ด์ฃผ์–ด์•ผ ํ•œ๋‹ค!!

 

 

 

CREATE TRIGGER ํŠธ๋ฆฌ๊ฑฐ๋ช…

AFTER UPDATE OF ๋ถ€๋ชจํ…Œ์ด๋ธ”์˜ PK ์ปฌ๋Ÿผ๋ช… ON ๋ถ€๋ชจํ…Œ์ด๋ธ”๋ช…  FOR EACH ROW

BEGIN

        UPDATE ์ž์‹ํ…Œ์ด๋ธ”๋ช…

        SET ๋ถ€๋ชจ์˜ PK๋ฅผ FK๋กœ ์“ฐ๋Š” ์ž์‹ํ…Œ์ด๋ธ”์˜ ์ปฌ๋Ÿผ๋ช… = :NEW.๋ถ€๋ชจํ…Œ์ด๋ธ”์˜ PK ์ปฌ๋Ÿผ๋ช…

        WHERE ๋ถ€๋ชจ์˜ PK๋ฅผ FK๋กœ ์“ฐ๋Š” ์ž์‹ํ…Œ์ด๋ธ”์˜ ์ปฌ๋Ÿผ๋ช… =:OLD.๋ถ€๋ชจํ…Œ์ด๋ธ”์˜ PK ์ปฌ๋Ÿผ๋ช…;

END;

 

 

 

์˜ˆ์‹œ)

 

์ผ๊ธฐ ํ…Œ์ด๋ธ”์˜ dauthor ๊ฐ€ ๋ฉค๋ฒ„ ํ…Œ์ด๋ธ”์˜ memid๋ฅผ  ์™ธ๋ž˜ ์ฐธ์กฐํ•œ๋‹ค.

 

CREATE TRIGGER dauthor_update

AFTER UPDATE OF memid ON member  FOR EACH ROW

BEGIN

        UPDATE diary

        SET dauthor = :NEW.memid

        WHERE dauthor =:OLD.memid;

END;

 

 

PARENT ํ…Œ์ด๋ธ”์ด UPDATE๋ ๋•Œ ํŠธ๋ฆฌ๊ฑฐ๋ฅผ ์ž‘๋™ ์‹œํ‚ค๊ณ ,

PARENT ํ…Œ์ด๋ธ”์˜ NAME ์ปฌ๋Ÿผ์„ FK๋กœ ๋ฌผ๊ณ ์žˆ๋Š” CHILD ํ…Œ์ด๋ธ”์— PARENT_NAME ์„ ์ƒˆ๋กœ์šด ๋ฐ์ดํ„ฐ๋กœ ๋ณ€๊ฒฝํ•ด๋ผ..

 

 

๋Œ“๊ธ€