{"id":1500,"date":"2009-12-26T17:27:22","date_gmt":"2009-12-26T08:27:22","guid":{"rendered":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/?p=1500"},"modified":"2011-09-14T13:23:04","modified_gmt":"2011-09-14T04:23:04","slug":"sqlite%e3%81%a7%e3%83%88%e3%83%aa%e3%82%ac%e3%83%bc3","status":"publish","type":"post","link":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/2009\/12\/26\/1500\/","title":{"rendered":"SQLite\u3067\u30c8\u30ea\u30ac\u30fc(3)"},"content":{"rendered":"

\uff08\u524d\u306e\u8a18\u4e8b\u304b\u3089\u306e\u7d9a\u304d\uff09<\/a><\/p>\n

\u767b\u9332\u65e5\u3068\u66f4\u65b0\u65e5\u306e\uff12\u3064\u306e\u8981\u7d20\u3092\u6301\u3064\u6b21\u306e\u30c6\u30fc\u30d6\u30eb<\/p>\n

CREATE TABLE <\/span>info <\/span>(<\/span>id <\/span>INTEGER PRIMARY KEY<\/span>, <\/span>name <\/span>text<\/span>, <\/span>regist DATETIME<\/span>, <\/span>modified DATETIME<\/span>)<\/span>;<\/span><\/pre><\/div><\/div>\n
\u3067\u3001\u305d\u308c\u305e\u308c\u3092\u30c8\u30ea\u30ac\u30fc\u3067\u66f4\u65b0\u3059\u308b\u305f\u3081\u306b\u3001\u524d\u8a18\u4e8b<\/a>\u3067\u306fINSERT\u30c8\u30ea\u30ac\u30fc\u304b\u3089UPDATE\u30c8\u30ea\u30ac\u30fc\u304c\u547c\u3070\u308c\u308b\u306e\u3092\u907f\u3051\u308b\u305f\u3081\u3001INSERT\u30c8\u30ea\u30ac\u30fc\u5185\u3067\u306fUPDATE\u305b\u305a\u3001NEW.id\u306e\u9805\u76ee\u3092\u524a\u9664\u3057\u3066\u3001\u65b0\u898f\u306b\u767b\u9332\u65e5\u3092\u8a2d\u5b9a\u3057\u3066\u3055\u3089\u306b\u65b0\u898f\u8ffd\u52a0\u3059\u308b\u3001\u3068\u3044\u3046\u3053\u3068\u3092\u884c\u306a\u3063\u305f\u3002\uff08\u4fdd\u5b58\u6642\u306b\u30a8\u30e9\u30fc\u306b\u306a\u308b\u554f\u984c<\/a>\u306e\u305f\u3081\u4e00\u6642\u7684\u306bSE_T<\/font>, WHER_E<\/font>, FRO_M<\/font>, INT_O<\/font>\u306a\u3069\u3068\u8868\u8a18\u3057\u3066\u307e\u3059\u3002\u4e86\u627f\u4e0b\u3055\u3044m(__)m\uff09<\/span><\/p>\n
CREATE TRIGGER <\/span>info_trig_regist <\/span>AFTER INSERT ON <\/span>info\r\n<\/span>BEGIN\r\n  DELETE <\/span>FRO_M info <\/span>WHERE <\/span>id<\/span>=<\/span>NEW<\/span>.<\/span>id<\/span>;\r\n  <\/span>INSERT <\/span>INT_O info <\/span>(<\/span>id<\/span>, <\/span>name<\/span>, <\/span>regist<\/span>, <\/span>modified<\/span>) <\/span>VALUES <\/span>(<\/span>null<\/span>, <\/span>NEW<\/span>.<\/span>name<\/span>, <\/span>datetime<\/span>(<\/span>'<\/span>now<\/span>'<\/span>, <\/span>'<\/span>localtime<\/span>'<\/span>)<\/span>, <\/span>NEW<\/span>.<\/span>modified<\/span>)<\/span>;\r\n<\/span>END<\/span>;\r\n\r\n<\/span>CREATE TRIGGER <\/span>info_trig_modified <\/span>AFTER UPDATE ON <\/span>info\r\n<\/span>BEGIN\r\n  UPDATE <\/span>info SE_T modified<\/span>=<\/span>datetime<\/span>(<\/span>'<\/span>now<\/span>'<\/span>, <\/span>'<\/span>localtime<\/span>'<\/span>) <\/span>WHER_E id<\/span>=<\/span>OLD<\/span>.<\/span>id<\/span>;\r\n<\/span>END<\/span>;<\/span><\/pre><\/div><\/div>\n
\u3053\u308c\u3067\u5e0c\u671b\u306e\u52d5\u4f5c\u3068\u306f\u306a\u3063\u305f\u306e\u3060\u304c\u3001\u65b0\u898f\u8ffd\u52a0\u3059\u308b\u969b\u306b\u767b\u9332\u65e5\u4ee5\u5916\u306e\u9805\u76ee\u3092NEW.xxx\u3068\u3057\u3066\u5217\u6319\u3057\u306a\u3051\u308c\u3070\u306a\u3089\u305a\u3001\u30c6\u30fc\u30d6\u30eb\u306e\u5909\u66f4\u306b\u3082\u5bfe\u5fdc\u3057\u306b\u304f\u3044\u3002<\/p>\n
\u305d\u3053\u3067\u3001\u5225\u306e\u6848\u3092\u8003\u3048\u3066\u307f\u305f\u3002<\/p>\n
[\u6848\uff11] \u4e00\u6642\u767b\u9332\u7528\u306e\u5225\u30c6\u30fc\u30d6\u30eb\u306b\u3044\u3063\u305f\u3093\u5168\u30b3\u30d4\u30fc\u3057\u3001\u305d\u306e\u8981\u7d20\u3092UPDATE\u3059\u308b<\/strong><\/p>\n
CREATE TABLE <\/span>info_tmp <\/span>(<\/span>id <\/span>INTEGER<\/span>, <\/span>name <\/span>TEXT<\/span>, <\/span>regist DATETIME<\/span>, <\/span>modified DATETIME<\/span>)<\/span>;\r\n\r\n<\/span>CREATE TRIGGER <\/span>info_trig_regist <\/span>AFTER INSERT ON <\/span>info\r\n<\/span>BEGIN\r\n  INSERT <\/span>INT_O info_tmp <\/span>SELECT<\/span> * <\/span>FRO_M info WHER_E id<\/span>=<\/span>NEW<\/span>.<\/span>id<\/span>;\r\n  <\/span>UPDATE <\/span>info_tmp SE_T regist<\/span>=<\/span>datetime<\/span>(<\/span>'<\/span>now<\/span>'<\/span>, <\/span>'<\/span>localtime<\/span>'<\/span>) <\/span>WHER_E id<\/span>=<\/span>NEW<\/span>.<\/span>id<\/span>;\r\n  <\/span>DELETE <\/span>FRO_M info WHER_E id<\/span>=<\/span>NEW<\/span>.<\/span>id<\/span>;\r\n  <\/span>INSERT <\/span>INT_O info <\/span>SELECT<\/span> * <\/span>FRO_M info_tmp<\/span>;\r\n  <\/span>DELETE <\/span>FRO_M info_tmp<\/span>;\r\n<\/span>END<\/span>;<\/span><\/pre><\/div><\/div>\n
\u3053\u306e\u30c8\u30ea\u30ac\u30fc\u3067\u306f\u3044\u3063\u305f\u3093\u300cSELECT * FRO_M info WHER_E id=NEW.id\u300d\u3067\u53d6\u308a\u51fa\u3057\u305f\u8981\u7d20\u3092\u3001info_tmp\u306b\u30b3\u30d4\u30fc\u3057\u3001\u305d\u3061\u3089\u306e\u767b\u9332\u65e5\u3092UPDATE\u3067\u66f4\u65b0\u3057\u305f\u3046\u3048\u3067\u3001info\u306b\u30b3\u30d4\u30fc\u3057\u76f4\u3057\u3066\u3044\u308b\u3002<\/p>\n
\u3053\u306e\u65b9\u6cd5\u306e\u307b\u3046\u304c\u3001\u9805\u76ee\u3092\u5217\u6319\u3057\u3066\u3044\u305f\u5148\u306e\u3082\u306e\u3088\u308a\u306f\u6c4e\u7528\u6027\u304c\u3042\u308b\u3068\u601d\u3046\u304c\u3001\u30b3\u30d4\u30fc\u7528\u3060\u3051\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u7528\u610f\u3059\u308b\u306e\u304c\u7f8e\u3057\u304f\u306a\u3044\u611f\u3058\u3002<\/p>\n
\u305d\u3082\u305d\u3082\u3001INSERT\u30c8\u30ea\u30ac\u30fc\u5185\u304b\u3089UPDATE\u30c8\u30ea\u30ac\u30fc\u304c\u547c\u3070\u308c\u308b\u306e\u304c\u554f\u984c\u306a\u306e\u3067\u3001\u305d\u306e\u5834\u5408\u306e\u307f\u3092\u9664\u5916\u3067\u304d\u308c\u3070\u3068\u308a\u3042\u3048\u305a\u306f\u554f\u984c\u306a\u3044\u3002<\/p>\n
\u305d\u3053\u3067\u3001\u6b21\u306e\u6848\u3002<\/p>\n
[\u6848\uff12] \u66f4\u65b0\u65e5\u7528\u306eUPDATE\u30c8\u30ea\u30ac\u30fc\u3092\u767b\u9332\u65e5\u304c\u8a2d\u5b9a\u3055\u308c\u305f\u5834\u5408\u306e\u307f\u52d5\u4f5c\u3055\u305b\u308b<\/strong>
\n\u767b\u9332\u65e5\u306fINSERT\u30c8\u30ea\u30ac\u30fc\u5185\u3067UPDATE\u3059\u308b\u3053\u3068\u306b\u3057\u3066\u3001\u305d\u3053\u304b\u3089UPDATE\u30c8\u30ea\u30ac\u30fc\u304c\u547c\u3070\u308c\u3066\u3082\u767b\u9332\u65e5\u304c\u8a2d\u5b9a\u3055\u308c\u3066\u3044\u306a\u3044\uff08\u7a7a\u306e\uff09\u5834\u5408\u306f\u305d\u306e\u30c8\u30ea\u30ac\u30fc\u306f\u8d77\u52d5\u3057\u306a\u3044\u3053\u3068\u306b\u3059\u308b\u3002<\/p>\n
CREATE TRIGGER <\/span>info_trig_regist <\/span>AFTER INSERT ON <\/span>info\r\n<\/span>BEGIN\r\n  UPDATE <\/span>info SE_T regist<\/span>=<\/span>datetime<\/span>(<\/span>'<\/span>now<\/span>'<\/span>, <\/span>'<\/span>localtime<\/span>'<\/span>) <\/span>WHER_E rowid<\/span>=<\/span>NEW<\/span>.<\/span>rowid<\/span>;\r\n<\/span>END<\/span>;\r\n\r\n<\/span>CREATE TRIGGER <\/span>info_trig_modified <\/span>BEFORE UPDATE ON <\/span>info\r\n  <\/span>WHEN OLD<\/span>.<\/span>regist<\/span><><\/span>''\r\n<\/span>BEGIN\r\n  UPDATE <\/span>info SE_T modified<\/span>=<\/span>datetime<\/span>(<\/span>'<\/span>now<\/span>'<\/span>, <\/span>'<\/span>localtime<\/span>'<\/span>) <\/span>WHER_E id<\/span>=<\/span>OLD<\/span>.<\/span>id<\/span>;\r\n<\/span>END<\/span>;<\/span><\/pre><\/div><\/div>\n
UPDATE\u30c8\u30ea\u30ac\u30fc\u306b\u300cWHEN OLD.regist<>”\u300d\u3068\u3057\u3066\u3001\u767b\u9332\u65e5\u304c\u7a7a\u3067\u306a\u3044\u5834\u5408\u306e\u307f\u3068\u3057\u3066\u6307\u5b9a\u3057\u305f\u3002<\/p>\n
\u3044\u3064\u3082\u4f7f\u3048\u308b\u6307\u5b9a\u3068\u3044\u3046\u308f\u3051\u3067\u306f\u306a\u3044\u6c17\u3082\u3059\u308b\u304c\u3001\u4eca\u56de\u306e\u5834\u5408\u306b\u306f\u3053\u306e\u65b9\u6cd5\u306e\u307b\u3046\u304c\u3059\u3063\u304d\u308a\u3057\u3066\u3044\u3066\u3044\u3044\u3088\u3046\u3060\u3002<\/p>\n
\uff08\u524d\u306e\u8a18\u4e8b\u304b\u3089\u306e\u7d9a\u304d\uff09 \u767b\u9332\u65e5\u3068\u66f4\u65b0\u65e5\u306e\uff12\u3064\u306e\u8981\u7d20\u3092\u6301\u3064\u6b21\u306e\u30c6\u30fc\u30d6\u30eb CREATE TABLE info (id INTEGER PRIMARY KEY, name text, regist DATETIME, modi […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[1],"tags":[123,171],"_links":{"self":[{"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/posts\/1500"}],"collection":[{"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/comments?post=1500"}],"version-history":[{"count":10,"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/posts\/1500\/revisions"}],"predecessor-version":[{"id":1511,"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/posts\/1500\/revisions\/1511"}],"wp:attachment":[{"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/media?parent=1500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/categories?post=1500"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mechsys.tec.u-ryukyu.ac.jp\/~oshiro\/SiteList\/wp-json\/wp\/v2\/tags?post=1500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
[\u8ffd\u8a18]
\n(SQLite3\u9650\u5b9a\u306b\u306a\u308b\u304c)INSERT\u6642\u306eDEFAULT\u6307\u5b9a\u306b\u3064\u3044\u3066\u8abf\u3079\u3066\u307f\u305f\uff08\u7d9a\u304f<\/a>\uff09\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"