{"id":139,"title":"\u89e6\u53d1\u5668","good":0,"bad":0,"hit":1788,"created_at":"2014-12-24 18:01:06","content":"<p>\u89e6\u53d1\u5668trigger<br\/>\u4e00\u79cd\u7f16\u7a0b\u8bbe\u8ba1\uff0c\u7c7b\u4f3cjs\u7684\u57fa\u4e8e\u4e8b\u4ef6\u7f16\u7a0b\uff1b<br\/>\u53ef\u4ee5\u5728\u67d0\u4e2a\u8868\u7684\u6bcf\u6b21\u8bb0\u5f55\u4e0a\u8bbe\u7f6e\u4e00\u4e2a\u4e8b\u4ef6\uff0c\u4ece\u800c\u5bf9\u8be5\u8868\u4e0a\u7684\u67d0\u6b64\u64cd\u4f5c\u52a0\u4ee5\u76d1\u542c\uff0c\u4e00\u65e6\u6240\u76d1\u542c\u7684\u884c\u4e3a\u51fa\u73b0\uff0c\u5219\u4f1a\u6267\u884c\u76f8\u5e94\u7684\u4ee3\u7801\uff1b<br\/><br\/>\u8bed\u6cd5<br\/>create trigger \u89e6\u53d1\u5668\u540d\u79f0 \u89e6\u53d1\u6761\u4ef6\uff0c\u76d1\u542c\u7684\u5185\u5bb9\uff0c\u89e6\u53d1\u540e\u7684\u64cd\u4f5c\uff1b<br\/>create trigger trigger_name<br\/>trigger_time trigger_event<br\/>on tablename for each row trigger_stmt<br\/><span style=\"color: rgb(255, 0, 0);\">\u4e00\u5171\u67096\u79cd\u4e8b\u4ef6<br\/>before insert<br\/>after&nbsp; insert<br\/>before update<br\/>after&nbsp; update<br\/>before delete<br\/>after&nbsp; delete<\/span><\/p><p><br\/>\u4f8b\u5b50\uff1astudent\u8868\u6bcf\u589e\u52a0\u4e00\u6761\u5b66\u751f\u8bb0\u5f55\u89e6\u53d1\u4e00\u4e2a\u8bb0\u5f55\u64cd\u4f5c\u65e5\u5fd7\u7684sql<br\/>create trigger test_trigger<br\/>after insert<br\/>on student for each row<br\/>insert into student_log values(null,&#39;insert&#39;,now())<br\/><br\/><br\/>\u5728\u89e6\u53d1\u7a0b\u5e8f\u4e2d\u5f97\u5230\u5f53\u524d\u89e6\u53d1\u7684\u8bb0\u5f55\u4fe1\u606f<br\/>\u7528new,old\u6765\u83b7\u5f97\u64cd\u4f5c\u524d\uff0c\u540e\u7684\u8bb0\u5f55<br\/><span style=\"color: rgb(255, 0, 0);\">insrt\u6ca1\u6709\u65e7\u8bb0\u5f55\uff0c\u53ea\u80fd\u7528new<br\/>update\u53ef\u4ee5\u7528new\u4e5f\u53ef\u4ee5\u7528old<br\/>delete\u6ca1\u6709\u65b0\u8bb0\u5f55\uff0c\u53ea\u6709old\u53ef\u7528<\/span><br\/><br\/>\u4f8b\u5b50\uff1a\u53c2\u7167\u4e0a\u9762\u7684\u4f8b\u5b50\uff0c\u5047\u5982\u6211\u4eec\u5220\u9664\u4e00\u6761\u6216\u591a\u6761\u5b66\u751f\u8bb0\u5f55\u7684\u65f6\u5019\uff0c\u5728\u65e5\u5fd7\u91cc\u8bb0\u5f55\u4e0b\u6b64\u4e2a\u6216\u6b64\u6279\u5b66\u751f\u7684id<br\/>create trigger trigger_del_student<br\/>after delete<br\/>on student for each row<br\/>insert into student_log values(null,&#39;delete&#39;,old.id)<br\/><span style=\"color: rgb(255, 0, 0);\">\u6ce8\u610f\uff1a\u89e6\u53d1\u5668\u7684\u6267\u884c\u662f\u5148\u5224\u65ad\u89e6\u53d1\u5668\u662f\u5426\u5b58\u5728\uff0c\u5b58\u5728\u624d\u6267\u884c\u540e\u9762\u7684sql,\u5426\u5219\u4e0d\u6267\u884c\uff1b<\/span><br\/><br\/>\u4f8b\u5b50\uff1a\u5f53\u8eab\u9ad8\u5927\u4e8e\u7b49\u4e8e1.75\u7684\u5b66\u751f\u4fe1\u606f\u88ab\u66f4\u65b0\u65f6\uff0c\u7528\u89e6\u53d1\u5668\u8bb0\u5f55\u4e0b\u6b64\u5b66\u751f\u7684id,\u66f4\u65b0\u524d\u540e\u7684\u8eab\u9ad8<br\/>create trigger trigger_updateheight<br\/>after update<br\/>on student for each row<br\/>if old.height&gt;=1.75 then<br\/>insert into student_log values(null,&#39;update&#39;,old.id,concat(old.height,&#39;-&#39;,new.height);<br\/>end if;<br\/><span style=\"color: rgb(255, 0, 0);\">\u7531\u4e8e\u4ee5\u4e0a\u8bed\u53e5\u6709\u4e24\u4e2a\u5c01\u53f7&#39;;&#39;\u6b64\u65f6\u4f1a\u51fa\u73b0\u95ee\u9898\uff1b\u53ef\u4ee5\u5728\u521b\u5efa\u89e6\u53d1\u5668\u524d\u66f4\u6362\u6700\u5916\u5c42\u7684\u5206\u9694\u7b26\uff0c\u91cc\u9762\u90a3\u4e2a\u5c01\u53f7\u662f\u6ca1\u529e\u6cd5\u4fee\u6539\u7684\uff0c\u90a3\u6837\u4f1a\u8bed\u6cd5\u9519\u8bef\uff1b<br\/>\u5219\u4e0a\u9762\u8fd9\u4e2a\u4f8b\u5b50\u5e94\u8be5\u8fd9\u6837\u64cd\u4f5c,\u5148\u5c06\u5206\u9694\u7b26\u6539\u6210$$\u7136\u540e\u518d\u8fd8\u539f\u4e3a\u5c01\u53f7&quot;;&quot;\u3002<\/span><br\/>delimiter $$<br\/>create trigger trigger_updateheight<br\/>after update<br\/>on student for each row<br\/>begin<br\/>if old.height&gt;=175 then<br\/>insert into student_log values(null,&#39;update&#39;,now(),old.id,concat(old.height,&#39;-&#39;,new.height));<br\/>end if;<br\/>end<br\/>$$<br\/>delimiter ;<br\/><br\/><span style=\"color: rgb(255, 0, 0);\">\u5220\u9664\u89e6\u53d1\u5668\uff1adrop trigger \u89e6\u53d1\u5668\u540d\u79f0<\/span><br\/><br\/>\u6ce8\u610f\uff1a<br\/>1\u3001\u4e00\u4e2a\u8868\u4e0a\u7684\u4e8b\u4ef6\u53ea\u80fd\u6709\u4e00\u4e2a\u89e6\u53d1\u5668\uff0c\u4e00\u4e2a\u8868\u6700\u591a\u6709\u516d\u4e2a\u89e6\u53d1\u5668\uff0c\u5982\u679c\u9700\u8981\uff0c\u53ea\u80fd\u5c06\u539f\u59cb\u7684\u5220\u9664\u518d\u65b0\u589e<br\/>2\u3001\u53ea\u8981\u4e8b\u4ef6\u53d1\u751f\uff0c\u89e6\u53d1\u7a0b\u5e8f\u5c31\u53ef\u80fd\u6267\u884c\uff0c\u4e00\u6761\u8bed\u53e5\u53ef\u80fd\u89e6\u53d1\u591a\u4e2a\u89e6\u53d1\u7a0b\u5e8f\u3002<br\/>\u4f8b\u5982\uff1a\u4e0b\u9762\u7684\u8bed\u53e5insert\u51b2\u7a81\u65f6\u4f1a\u8fdb\u884cupdate\u64cd\u4f5c<br\/>insert into on duplicate key update<br\/><\/p>"}