{"id":1724,"date":"2008-06-23T04:27:37","date_gmt":"2008-06-23T04:27:37","guid":{"rendered":"http:\/\/dev.jblove.net\/?p=1724"},"modified":"2008-06-23T04:27:37","modified_gmt":"2008-06-23T04:27:37","slug":"dbms-mysql%ed%8a%b8%eb%a6%ac%ea%b1%b0%eb%a5%bc-%ec%9d%b4%ec%9a%a9%ed%95%9c-%eb%82%a0%ec%a7%9c-%ec%9e%90%eb%8f%99-%ec%97%85%eb%8d%b0%ec%9d%b4%ed%8a%b8","status":"publish","type":"post","link":"https:\/\/jblove.net\/?p=1724","title":{"rendered":"[MYSQL]\ud2b8\ub9ac\uac70\ub97c \uc774\uc6a9\ud55c \ub0a0\uc9dc \uc790\ub3d9 \uc5c5\ub370\uc774\ud2b8"},"content":{"rendered":"

\ub9ce\uc740 \ubd84\ub4e4\uc774 \uc544\uc2dc\uaca0\uc9c0\ub9cc \ubaa8\ub974\uc2dc\ub294 \ubd84\ub4e4\uc744 \uc704\ud574 \ud301\uc5d0 \uc62c\ub9bd\ub2c8\ub2e4.
\n
\ucd08\ubcf4\uac00 \uc544\ub2cc \ubd84\uc740 \ub4a4\ub85c \ubc84\ud2bc\uc744 \ub20c\ub7ec\uc8fc\uc2dc\uba74 \ub418\uaca0\uc2b5\ub2c8\ub2e4 ^^;<\/p>\n

mysql 5.x \ub300\ubd80\ud130 \ub9ce\uc740 \ubcc0\ud654\uac00 \uc788\uc5c8\ub294\ub370\uc694 \uc774 \uc911 Trigger\uc5d0 \ub300\ud574 \uc62c\ub9b4\uae4c \ud569\ub2c8\ub2e4.
\n
Trigger\ub780 \ud2b9\uc815 \ud14c\uc774\ube14\uc5d0 \uc785\ub825, \uc218\uc815, \uc0ad\uc81c\uac00 \uc774\ub8e8\uc5b4 \uc84c\uc744 \uacbd\uc6b0 \ud2b9\uc815 \uba85\ub839\uc744 \uc2e4\ud589\ud558\ub294 \uae30\ub2a5\uc778\ub370\uc694,
\n
\uc774 \ud2b8\ub9ac\uac70<\/span>\ub97c \uc774\uc6a9\ud558\uc5ec \ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud0c0\ub97c \uc218\uc815\ud588\uc744 \uacbd\uc6b0 \uc218\uc815\ud55c \ub0a0\uc9dc\ub97c \uc790\ub3d9\uc73c\ub85c \uae30\ub85d\ud558\ub294 \uc608\uc81c\ub97c \uc2e4\ud589\ud574 \ubcf4\ub3c4\ub85d \ud558\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

\ud2b8\ub9ac\uac70<\/span>\ub97c \uc774\uc6a9\ud558\uc9c0 \uc54a\uace0\ub3c4 DEFAULT CURRENT_TIMESTAMP UPDATE ON CURRENT_TIMESTAMP \ub97c \uc0ac\uc6a9\ud558\uba74 \ud574\uacb0\ub418\uaca0\uc73c\ub098,
\n
\uc774\ubbf8 \uc785\ub825\ub0a0\uc9dc\ub97c DEFAULT CURRENT_TIMESTAMP\ub85c \ud588\uc744 \uacbd\uc6b0 \ub450 \uce7c\ub7fc\uc758 \ub0a0\uc9dc\ub97c \uc790\ub3d9\uc73c\ub85c \uc785\ub825\uc774 \ubd88\uac00\ub2a5\ud560\ub54c \uc0ac\uc6a9\ud558\uc2dc\uba74 \ub418\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

[mysql]\ud2b8\ub9ac\uac70<\/span>\ub97c \uc774\uc6a9\ud55c \ub0a0\uc9dc \uc790\ub3d9 \uc5c5\ub370\uc774\ud2b8<\/p>\n

\uc900\ube44\uc0ac\ud56d : mysql 5.0 \uc774\uc0c1
\n
*\/<\/p>\n

— \ud2b8\ub9ac\uac70<\/span>\uc6a9 \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14\uc744 \ub9cc\ub4ed\ub2c8\ub2e4
\n
DROP TABLE TriggerTest;
\n
CREATE TABLE TriggerTest (
\n
  pk INTEGER AUTO_INCREMENT PRIMARY KEY,
\n
    col01 VARCHAR(12) NOT NULL,
\n
    regDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, — \uc785\ub825\uc2dc
\n\uc790\ub3d9\uc73c\ub85c \ub0a0\uc9dc\ub97c \uae30\uc785\ud558\uae30 \uc704\ud574 timestamp \ud0c0\uc785\uacfc \uae30\ubcf8 \uc785\ub825\uac12\uc73c\ub85c current_timestamp\ub97c \uc0ac\uc6a9\ud588\uc2b5\ub2c8\ub2e4
\n
    modifyDate TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00’
\n
)<\/p>\n

— \ud2b8\ub9ac\uac70<\/span>\ub97c \ub9cc\ub4ed\ub2c8\ub2e4.
\n
— \uae30\ubcf8 \ud2b8\ub9ac\uac70<\/span> \ubb38\ubc95\uc740 CREATE TRIGGER [\ud2b8\ub9ac\uac70<\/span>\uc774\ub984] [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON [\ud14c\uc774\ube14\uc774\ub984] FOR EACH ROW [\uc2e4\ud589\ubb38] \uc785\ub2c8\ub2e4
\n
—
\n
CREATE TRIGGER TRG_TriggerTest_UPDATE
\n
BEFORE UPDATE ON TriggerTest
\n
FOR EACH ROW
\n
BEGIN
\n
    SET NEW.modifyDate = CURRENT_TIMESTAMP;
\n
END;<\/p>\n

— \ud14c\uc2a4\ud2b8\uc6a9 \ub370\uc774\ud0c0\ub97c \uc785\ub825\ud569\ub2c8\ub2e4.
\n
INSERT INTO TriggerTest SET col01 = ‘123’;
\n
INSERT INTO TriggerTest SET col01 = ‘456’;<\/p>\n

SELECT * FROM TriggerTest;<\/p>\n

— \ub300\ub7b5 \uc544\ub798\uc640 \uac19\uc774 \uc2e4\ud589\ub418\uc5c8\uc73c\ub9ac\ub77c \uc608\uc0c1\ud569\ub2c8\ub2e4.
\n
pk    col01    regDate                      modifyDate
\n
——————————————————————————————————————
\n
1    123    “2008-03-20 \uc624\uc804 11:33:44”    ‘0000-00-00 00:00:00’
\n
2    456    “2008-03-20 \uc624\uc804 11:33:44”    ‘0000-00-00 00:00:00’
\n
——————————————————————————————————————<\/p>\n

–\uc774\uc81c PK\uac00 2\uc778 COL01 \uc758 \uce7c\ub7fc\uc744 \uc218\uc815\ud558\uaca0\uc2b5\ub2c8\ub2e4.
\n
UPDATE TriggerTest SET col01 = ‘012’ where pk = 2;<\/p>\n

— \uacb0\uacfc\ub97c \ubcf4\ub3c4\ub85d \ud558\uc8e0.
\n
SELECT * FROM TriggerTest;<\/p>\n

\uc544\ub798\uc640 \uac19\uc774 \uc218\uc815\ud55c \uce7c\ub7fc\uc758 \uc218\uc815\uc77c\uc774 \uc800\uc7a5\ub418\uc5c8\uc2b5\ub2c8\ub2e4.
\n
pk    col01    regDate                      modifyDate
\n
——————————————————————————————————————
\n
1    123    “2008-03-20 \uc624\uc804 11:33:44”    ‘0000-00-00 00:00:00’
\n
2    012    “2008-03-20 \uc624\uc804 11:33:44”    “2008-03-20 \uc624\uc804 11:36:34”
\n
——————————————————————————————————————<\/p>\n

\uc774 \ud2b8\ub9ac\uac70<\/span>\ub97c \uc880\ub354 \ud65c\uc6a9\ud558\uba74 \ud2b9\uc815 \ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud0c0\uac00 \ub4e4\uc5b4\uc624\uba74 \uc790\ub3d9\uc73c\ub85c \ub2e4\ub978 \ud14c\uc774\ube14\uc5d0 \uc785\ub825, \uc218\uc815, \uc0ad\uc81c\ub3c4 \uac00\ub2a5\ud569\ub2c8\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"

\ub9ce\uc740 \ubd84\ub4e4\uc774 \uc544\uc2dc\uaca0\uc9c0\ub9cc \ubaa8\ub974\uc2dc\ub294 \ubd84\ub4e4\uc744 \uc704\ud574 \ud301\uc5d0 \uc62c\ub9bd\ub2c8\ub2e4. \ucd08\ubcf4\uac00 \uc544\ub2cc \ubd84\uc740 \ub4a4\ub85c \ubc84\ud2bc\uc744 \ub20c\ub7ec\uc8fc\uc2dc\uba74 \ub418\uaca0\uc2b5\ub2c8\ub2e4 ^^; mysql 5.x \ub300\ubd80\ud130 \ub9ce\uc740 \ubcc0\ud654\uac00 \uc788\uc5c8\ub294\ub370\uc694 \uc774 \uc911 Trigger\uc5d0 \ub300\ud574 \uc62c\ub9b4\uae4c \ud569\ub2c8\ub2e4. Trigger\ub780 \ud2b9\uc815 \ud14c\uc774\ube14\uc5d0 \uc785\ub825, \uc218\uc815, \uc0ad\uc81c\uac00 \uc774\ub8e8\uc5b4 \uc84c\uc744 \uacbd\uc6b0 \ud2b9\uc815 \uba85\ub839\uc744 \uc2e4\ud589\ud558\ub294 \uae30\ub2a5\uc778\ub370\uc694, \uc774 \ud2b8\ub9ac\uac70\ub97c \uc774\uc6a9\ud558\uc5ec \ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud0c0\ub97c \uc218\uc815\ud588\uc744 \uacbd\uc6b0 \uc218\uc815\ud55c \ub0a0\uc9dc\ub97c \uc790\ub3d9\uc73c\ub85c \uae30\ub85d\ud558\ub294 \uc608\uc81c\ub97c \uc2e4\ud589\ud574 \ubcf4\ub3c4\ub85d […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[51],"tags":[103,104],"class_list":["post-1724","post","type-post","status-publish","format-standard","hentry","category-papa-php","tag-dbms","tag-104"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/jblove.net\/index.php?rest_route=\/wp\/v2\/posts\/1724","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jblove.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jblove.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jblove.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jblove.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1724"}],"version-history":[{"count":0,"href":"https:\/\/jblove.net\/index.php?rest_route=\/wp\/v2\/posts\/1724\/revisions"}],"wp:attachment":[{"href":"https:\/\/jblove.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1724"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jblove.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1724"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jblove.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1724"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}