Home  >  Article  >  Database  >  MySQL触发器的创建与删除

MySQL触发器的创建与删除

WBOY
WBOYOriginal
2016-06-07 16:13:34941browse

下面的文章主要描述的是MySQL触发器的正确创建步骤,MySQL触发器的删除,你如果对MySQL触发器的正确创建步骤,MySQL触发器的删除有兴趣的话你就可以点击以下的文章进行观看了。 1、创建MySQL触发器: 语法: CREATETRIGGERtrigger_nametrigger_timetrigger_eve

下面的文章主要描述的是MySQL触发器的正确创建步骤,MySQL触发器的删除,你如果对MySQL触发器的正确创建步骤,MySQL触发器的删除有兴趣的话你就可以点击以下的文章进行观看了。

1、创建MySQL触发器:

语法:

<ol class="dp-xml">
<li class="alt"><span><span>CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name   </span></span></li>
<li><span>FOR EACH ROW   </span></li>
<li class="alt"><span>BEGIN   </span></li>
<li><span>trigger_stmt   </span></li>
<li class="alt"><span>END;   </span></li>
<li><span>CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name  </span></li>
<li class="alt"><span>FOR EACH ROW   </span></li>
<li><span>BEGIN  </span></li>
<li class="alt"><span>trigger_stmt  </span></li>
<li><span>END;  </span></li>
</ol>

例子

<ol class="dp-xml">
<li class="alt"><span><span>CREATE TRIGGER SetUserHome after insert ON users   </span></span></li>
<li><span>FOR EACH ROW   </span></li>
<li class="alt"><span>BEGIN   </span></li>
<li>
<span>update `users` set </span><span class="attribute">homeLocationX</span><span> = </span><span class="attribute-value">128</span><span>,  </span>
</li>
<li class="alt">
<span class="attribute">homeLocationY</span><span>=</span><span class="attribute-value">128</span><span>, </span><span class="attribute">homeLocationZ</span><span>=</span><span class="attribute-value">30</span><span>   </span>
</li>
<li>
<span>where </span><span class="attribute">uuid</span><span> = </span><span class="attribute-value">NEW</span><span>.uuid  </span>
</li>
<li class="alt"><span>END  </span></li>
</ol>

以上的例子是错误的, 让本表进行触发时进行更新会让程序进入死循环。

系统会报这样的错误:it is already used by statement which invoked this stored function/trigger.

应该改成以下语句:

<ol class="dp-xml">
<li class="alt"><span><span>CREATE TRIGGER SetUserHome before insert ON users   </span></span></li>
<li><span>FOR EACH ROW   </span></li>
<li class="alt"><span>BEGIN   </span></li>
<li>
<span>set </span><span class="attribute">New.homeLocationX</span><span> = </span><span class="attribute-value">128</span><span>;  </span>
</li>
<li class="alt">
<span>set </span><span class="attribute">New.homeLocationY</span><span> = </span><span class="attribute-value">128</span><span>;  </span>
</li>
<li>
<span>set </span><span class="attribute">New.homeLocationZ</span><span>=</span><span class="attribute-value">30</span><span>;   </span>
</li>
<li class="alt"><span>END  </span></li>
</ol>

大写的为关键字

trigger_name:触发器的名字,我常用的命名规则t_name_tableName_(b|a)(i|u|d),t:MySQL触发器标识,name:英文名,tableName:表名,b(BEFORE):标识是触发事件之前,a(AFTER):标识触发事件之后,i(insert):标识insert事件,u(update):标识update事件,d(delete):标识delete事件;

trigger_time:触发时间(BEFORE或AFTER)

trigger_event:事件名(insert或update或delete)

tbl_name:表名(必须是永久性表)

trigger_stmt:执行语句(可以是复合语名),使用别名OLD和NEW,能够引用与触发程序相关的表中的列。

2、删除解发器

语法:

<ol class="dp-xml"><li class="alt"><span><span>DROP TRIGGER [schema_name.]trigger_name;  </span></span></li></ol>

注意:以上操作均需SUPER权限

示例:

<ol class="dp-xml">
<li class="alt"><span><span>DROP TRIGGER t_wiley_hotelComment_bu;   </span></span></li>
<li><span>delimiter //   </span></li>
<li class="alt"><span>CREATE TRIGGER t_wiley_hotelComment_bu BEFORE UPDATE ON hotel_comment   </span></li>
<li><span>FOR EACH ROW   </span></li>
<li class="alt"><span>BEGIN   </span></li>
<li>
<span>IF </span><span class="attribute">OLD.ispass</span><span>=</span><span class="attribute-value">0</span><span> && </span><span class="attribute">NEW.ispass</span><span>=</span><span class="attribute-value">1</span><span> THEN   </span>
</li>
<li class="alt">
<span>UPDATE hotel_info SET </span><span class="attribute">sumcomment</span><span class="attribute-value">sumcomment</span><span>=sumcomment+1,<p></p></span><span class="attribute">sumconsume</span><span class="attribute-value">sumconsume</span><span>=sumconsume+NEW.consume,</span><span class="attribute">sumservice</span><span class="attribute-value">sumservice</span><span>=sumservice+NEW.service, <p></p></span><span class="attribute">sumroom</span><span class="attribute-value">sumroom</span><span>=sumroom+NEW.room,</span><span class="attribute">sumentiron</span><span class="attribute-value">sumentiron</span><span>=sumentiron+NEW.entironment,<p></p></span><span class="attribute">total</span><span class="attribute-value">total</span><span>=total+(NEW.service+NEW.room+NEW.entironment) WHERE </span><span class="attribute">hotel_id</span><span>=</span><span class="attribute-value">NEW</span><span>.hotel_id;   </span>
</li>
<li>
<span>ELSEIF </span><span class="attribute">OLD.ispass</span><span>=</span><span class="attribute-value">1</span><span> && </span><span class="attribute">NEW.ispass</span><span>=</span><span class="attribute-value">0</span><span> THEN   </span>
</li>
<li class="alt">
<span>UPDATE hotel_info SET </span><span class="attribute">sumcomment</span><span class="attribute-value">sumcomment</span><span>=sumcomment-1,<p></p></span><span class="attribute">sumconsume</span><span class="attribute-value">sumconsume</span><span>=sumconsume-NEW.consume,</span><span class="attribute">sumservice</span><span class="attribute-value">sumservice</span><span>=sumservice-NEW.service, <p></p></span><span class="attribute">sumroom</span><span class="attribute-value">sumroom</span><span>=sumroom-NEW.room,</span><span class="attribute">sumentiron</span><span class="attribute-value">sumentiron</span><span>=sumentiron-NEW.entironment,<p></p></span><span class="attribute">total</span><span class="attribute-value">total</span><span>=total-(NEW.service+NEW.room+NEW.entironment) WHERE </span><span class="attribute">hotel_id</span><span>=</span><span class="attribute-value">NEW</span><span>.hotel_id;   </span>
</li>
<li><span>END IF;   </span></li>
<li class="alt"><span>END;//   </span></li>
<li><span>delimiter ;  </span></li>
</ol>

以上的相关内容就是对MySQL触发器的使用的介绍,望你能有所收获。


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn