SQL) Trigger(ํŠธ๋ฆฌ๊ฑฐ) ๐Ÿ”ซ

yejin25ยท2021๋…„ 9์›” 28์ผ
0

๐Ÿ“Œ ํŠธ๋ฆฌ๊ฑฐ๊ฐ€ ๋ญ์ง€

ํŠน์ • ํ…Œ์ด๋ธ”์— INSERT, DELETE, UPDATE ๊ฐ™์€ DML ๋ฌธ์ด ์ˆ˜ํ–‰๋˜์—ˆ์„ ๋•Œ DB์—์„œ ์ž๋™์œผ๋กœ ๋™์ž‘ํ•˜๋„๋ก ์ž‘์„ฑ๋œ ํ”„๋กœ๊ทธ๋žจ

๊ทธ๋‹ˆ๊น ์–ด๋–ค ์ด๋ฒคํŠธ๊ฐ€ ๋ฐœ์ƒํ•˜๋ฉด ์ž๋™์œผ๋กœ ๋ฌด์–ธ๊ฐˆ ์ˆ˜ํ–‰ํ•œ๋‹ค๋Š” ์†Œ๋ฆฌ์ธ๊ฑฐ ๊ฐ™๋‹ค.

  • ๋ฌธ์žฅ ํŠธ๋ฆฌ๊ฑฐ
    ๋งŽ์€ ํ–‰์— ๋Œ€ํ•œ ๋ณ€๊ฒฝ ์ž‘์—…์ด ๋ฐœ์ƒํ•˜๋”๋ผ๋„ ์˜ค์ง ํ•œ๋ฒˆ๋งŒ ํŠธ๋ฆฌ๊ฑฐ๋ฅผ ๋ฐœ์ƒ์‹œํ‚ค๋Š” ๋ฐฉ๋ฒ•

ex) UPDATE study SET grade = grade + 10 ;
์ด ๋ฌธ์žฅ์ด ์‹คํ–‰๋จ์œผ๋กœ ์—ฌ๋Ÿฌํ–‰์ด ๋ณ€๊ฒฝ๋  ๊ฒฝ์šฐ ํŠธ๋ฆฌ๊ฑฐ๋Š” ๋‹จ ํ•œ๋ฒˆ๋งŒ ์‹คํ–‰

  • ํ–‰ ํŠธ๋ฆฌ๊ฑฐ
    ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ์—ฌ๋Ÿฌ ๊ฐœ์˜ ํ–‰์— ๋Œ€ํ•ด ํŠธ๋ฆฌ๊ฑฐ๋ฅผ ๋ฐ˜๋ณต์ ์œผ๋กœ ์—ฌ๋Ÿฌ๋ฒˆ ์ˆ˜ํ–‰
    ๋ณ€๊ฒฝ ํ›„์˜ ํ–‰์€ OLD, NEW ๋ฅผ ํ†ตํ•ด ๊ฐ€์ ธ์˜ฌ ์ˆ˜ ์žˆ์Œ

๐Ÿงท ํŠธ๋ฆฌ๊ฑฐ ํ˜•์‹

CREATE [OR REPLACE] TRIGGER ํŠธ๋ฆฌ๊ฑฐ๋ช…
	(BEFORE|AFTER) (INSERT|DELETE|UPDATE)
    ON ํ…Œ์ด๋ธ”๋ช…
    [FOR EACH ROW]
    [WHEN ์กฐ๊ฑด์‹]
    BEGIN
END;


๐Ÿ“Œ ํŠธ๋ฆฌ๊ฑฐ ์‚ฌ์šฉํ•ด๋ณด๊ธฐ

CREATE TRIGGER `latest_activity` AFTER INSERT ON `activity` FOR EACH ROW BEGIN
    DECLARE latest TINYINT(1); - (1)

    SELECT EXISTS (
        SELECT * FROM `cow_activity` WHERE `cow_id` = NEW.cow_id AND `farm_id` = NEW.farm_id AND `type` = NEW.type AND `activity_date` > NEW.activity_date
    ) INTO `latest`; - (2)

    IF `latest` = 0 THEN
        INSERT INTO `latest_activity`(`cow_id`, `farm_id`, `activity_date`, `type`, `value`)
	    VALUES(NEW.cow_id, NEW.farm_id, NEW.activity_date, NEW.type, NEW.value) ON DUPLICATE KEY UPDATE `activity_date` = NEW.activity_date, `value` = NEW.value;
    END IF; - (3)
END;

-> ๋ฐ์ดํ„ฐ๊ฐ€ ๋“ค์–ด์™”์„ ๋•Œ ์ด์ „ ๋ฐ์ดํ„ฐ์˜ ๋‚ ์งœ๋ณด๋‹ค ์ดํ›„์˜ ๋‚ ์งœ์ด๋ฉด ์ฆ‰, ์ตœ๊ทผ ๋ฐ์ดํ„ฐ์ด๋ฉด ๋ฐ์ดํ„ฐ๋ฅผ ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”์— ์ €์žฅํ•˜๋„๋ก ํ•˜๋Š” ํŠธ๋ฆฌ๊ฑฐ

(1) TINYINT ๋ณ€์ˆ˜ ์„ ์–ธ
(2) ์ตœ๊ทผ์— ๋“ค์–ด์˜จ ๋ฐ์ดํ„ฐ๊ฐ€ ์ด์ „ ๋ฐ์ดํ„ฐ๋ณด๋‹ค ๋‚ ์งœ๊ฐ€ ์ด์ „์ธ ํŠœํ”Œ์ด ์žˆ๋‹ค๋ฉด latest ๋ณ€์ˆ˜์— 1(true)์ด ๋“ค์–ด๊ฐ (์–ด์ง€๊ฐ„ํ•˜๋ฉด ์žˆ์„ ๋ฆฌ ์—†์Œ)
(3) ๋งŒ์•ฝ false ๋ผ๋ฉด ์ตœ๊ทผ์— ๋“ค์–ด์˜จ ๋ฐ์ดํ„ฐ๋ฅผ ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”์— INSERT ํ•˜๋„๋ก SQL ๋ฌธ์„ ์‹คํ–‰

2021-10-29 ์ถ”๊ฐ€

ON DUPLICATE KEY UPDATE `activity_date` = NEW.activity_date, `value` = NEW.value;

ํ‚ค๊ฐ€ ์ค‘๋ณต๋  ๋•Œ ํ‚ค ๊ฐ’์„ ์—…๋ฐ์ดํŠธ ํ•˜๊ณ  value ์ปฌ๋Ÿผ์˜ ๊ฐ’์„ insert ํ•ด์ฃผ๋Š” ๋ฌธ์žฅ์ธ์ค„ ์•Œ์•˜์œผ๋‚˜, ์ค‘๋ณต๋˜๋Š” ํ‚ค๊ฐ€ ์ด๋ฏธ ํ…Œ์ด๋ธ”์— ์กด์žฌํ•˜๋ฉด value ์ปฌ๋Ÿผ์˜ ๊ฐ’๋งŒ ๋ณ€๊ฒฝํ† ๋ก ํ•˜๋Š” ๋ฌธ์žฅ์ด์—ˆ๋‹ค.


์ด๋ฅผ ํŒŒ์•…ํ•˜์ง€ ๋ชปํ•˜๊ณ  esle ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ์กด์žฌํ•˜๋Š” ์ปฌ๋Ÿผ์ด๋ฉด update ํ•˜๋„๋ก ์ž‘์„ฑํ–ˆ์—ˆ๋Š”๋ฐ ๊ทธ๋Ÿด ํ•„์š”๊ฐ€ ์—†์—ˆ๋˜ ๊ฒƒ.


์œ„ ์‚ฌ์‹ค์„ ๊นจ๋‹ซ๊ฒŒ ํ•ด์ค€ Trigger ๋ฌธ ๐Ÿ‘‡

CREATE DEFINER=`root`@`localhost` TRIGGER `log` AFTER INSERT ON `index_log` FOR EACH ROW BEGIN
	INSERT INTO `index_logs`(`idx`, `date`, `year`, `half_year`, `month`, `week`, `day`, `total`) VALUE(NEW.idx, STR_TO_DATE(CONCAT(NEW.year, '-', NEW.month, '-', NEW.day), '%Y-%m-%d'), NEW.year, IF(NEW.month < 7, 1, 2), NEW.month, NEW.week, NEW.day, 1)
	ON DUPLICATE KEY UPDATE `total` = `total` + 1;
END;

0๊ฐœ์˜ ๋Œ“๊ธ€