当前位置：Gxlcms > 数据库问题 > 猎八哥浅谈MYSQL触发器

猎八哥浅谈MYSQL触发器

时间：2021-07-01 10:21:17 帮助过：5人阅读

CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `zongfen` int(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk 学生备份表 CREATE TABLE `studentbackup` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `zongfen` int(255) DEFAULT NULL, deletetime datetime, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk 创建触发器 CREATE trigger deletestudent before delete on student for each ROW begin insert into studentbackup (id,name,zongfen,deletetime) values(OLD.id,OLD.name,OLD.zongfen,NOW()); end;

解释：
CREATE trigger deletestudent是指创建一个名为deletestudent的触发器，由于是在执行之前做备份所以此处使用before，

delete on student是指student执行delete行为时有触发行为，
for each row的汉语意思是每一行，用在这里表示当每一行受到影响时，触发器都会被启动，

begin，end是触发器开始和结束的标记，

begin和end之间的语句是触发器被调动后所执行的行为，这里新增了一个字段deletetime，它用来记录删除时间，有关mysql中时间的调取方法我会在其他博客中做详细说明。

执行删除语句

delete from student where id=3;

执行完这条delete后，我们会发现学生表的一条信息被删除，而这条删除的信息在备份表中被插入。

 删除一条信息前后两表的信息

技术分享

删除之前

技术分享

删除之后

 2.案例二，使用触发器用来数据同步。

 场景：在学生表中有四条信息，学生1表中有四条相同的信息，当学生表中插入一条信息时，学生1表中会产生一条相同的新的信息（即学生表1和学生表的信息均相同）。执行insert时产生触发行为。

  1. 学生表1<br>CREATE TABLE `student1` (
  2.   `id` int(11) NOT NULL,
  3.   `name` varchar(255) DEFAULT NULL,
  4.   `zongfen` int(255) DEFAULT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=MyISAM DEFAULT CHARSET=gbk<br><br>创建触发器
  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">trigger</span> insertstudent after <span style="color: #0000ff">insert</span> <span style="color: #0000ff">on</span><span style="color: #000000"> student
  2. </span><span style="color: #0000ff">for</span><span style="color: #000000"> each ROW
  3. </span><span style="color: #0000ff">begin</span> 
  4.     <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span><span style="color: #000000"> student1 (id,name,zongfen)
  5. </span><span style="color: #0000ff">values</span><span style="color: #000000">(new.id,new.name,new.zongfen);
  6. </span><span style="color: #0000ff">end</span>;

执行插入语句

  1. <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> student (id,name,zongfen) <span style="color: #0000ff">values</span>(<span style="color: #800000; font-weight: bold">5</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">e</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">195</span>);

插入前后两表内容：

技术分享

插入之前

技术分享

插入之后

　　

3.案例三：使用触发器用来数据备份和同步。

更改学生表的信息，这个情况比案例一和案例二都较为复杂。因为当进行学生表信息更新时，在更改之前要将信息进行备份，更改之后要将更改后的信息更新到同步表中。话不多说直接上代码。

  1. <span style="color: #0000ff">在更新之前将数据备份<br>CREATE</span> <span style="color: #0000ff">trigger</span> updatestudent before <span style="color: #0000ff">update</span> <span style="color: #0000ff">on</span><span style="color: #000000"> student
  2. </span><span style="color: #0000ff">for</span><span style="color: #000000"> each ROW
  3. </span><span style="color: #0000ff">begin</span> 
  4.     <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span><span style="color: #000000"> studentbackup (id,name,zongfen,deletetime)
  5.         </span><span style="color: #0000ff">values</span><span style="color: #000000">(old.id,old.name,old.zongfen,NOW());
  6. </span><span style="color: #0000ff">end</span><span style="color: #000000">;
  7. 更新之后将数据同步到同步表中，备注一下：我们暂不考虑更新时更改id这种行为，</span>
  1. <span>因为id没有改变，</span><em id="__mceDel"><span>所以这里where id = old.id;或者where id = new.id;均正确，</span></em>
  1. <em id="__mceDel"><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">trigger</span> updatestudent1 after <span style="color: #0000ff">update</span> <span style="color: #0000ff">on</span><span style="color: #000000"> student
  2. </span><span style="color: #0000ff">for</span><span style="color: #000000"> each ROW
  3. </span><span style="color: #0000ff">begin</span> 
  4.     <span style="color: #0000ff">UPDATE</span> student1 <span style="color: #0000ff">set</span> name <span style="color: #808080">=</span> new.name,zongfen <span style="color: #808080">=</span><span style="color: #000000"> new.zongfen
  5.         </span><span style="color: #0000ff">where</span> id <span style="color: #808080">=</span><span style="color: #000000"> old.id;
  6. </span><span style="color: #0000ff">end</span>;<br><br>执行更新语句</em>
  1. <span style="color: #0000ff">UPDATE</span> student <span style="color: #0000ff">set</span> name <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">z</span><span style="color: #ff0000">‘</span>,zongfen <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">100</span> <span style="color: #0000ff">where</span> id <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">4</span>;

 

数据更新前后：

技术分享

更新之前

技术分享

更新之后

 

 :4.案例四：将学生表中成绩最高的学生信息，导入到maxgrade中去。话不多说，直接上代码。

  1. <span style="color: #0000ff">begin</span> 
  2.     <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">from</span><span style="color: #000000"> maxgrade;
  3.     </span><span style="color: #0000ff">select</span> <span style="color: #ff00ff">max</span>(zongfen) <span style="color: #0000ff">into</span> <span style="color: #008000">@maxzongfen</span> <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  4.     </span><span style="color: #0000ff">select</span> id <span style="color: #0000ff">into</span> <span style="color: #008000">@idmax</span> <span style="color: #0000ff">from</span> student <span style="color: #0000ff">where</span> zongfen<span style="color: #808080">=</span><span style="color: #008000">@maxzongfen</span><span style="color: #000000">;
  5.     </span><span style="color: #0000ff">select</span> name <span style="color: #0000ff">into</span> <span style="color: #008000">@namemax</span> <span style="color: #0000ff">from</span> student <span style="color: #0000ff">where</span> zongfen<span style="color: #808080">=</span><span style="color: #008000">@maxzongfen</span><span style="color: #000000">;
  6.     </span><span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> maxgrade(id,name,zongfen)value(<span style="color: #008000">@idmax</span>,<span style="color: #008000">@namemax</span>,<span style="color: #008000">@maxzongfen</span><span style="color: #000000">);
  7. </span><span style="color: #0000ff">end</span><span style="color: #000000">;
  8. </span><span style="color: #0000ff">insert</span> student <span style="color: #0000ff">values</span>(<span style="color: #800000; font-weight: bold">4</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">d</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">177</span>);

技术分享

之前

技术分享

之后

触发器的特点：

1.触发行为只能在之前或者之后执行（before，after）。

2.触发器的命名可以重复（这里的重复是指两个不同的表可以起一个相同的触发器名字，但是每个表中的不同触发器的命名必须不同；但从程序的可读性等发面考虑还是应该在整个数据库中每个触发器的命名都应该不同，这种命名重复在我看来是MYSQL的弊端）。

3.触发器可以产生增删改三种行为。

4.触发器不能被覆盖或者修改，要更改触发器时需要先删除它，然后新建。

删除语句drop TRIGGER 触发器名字;

5.begin和end之间可以插入多条sql语句。

6.触发器内不能调用存储过程。

7.每张表最多可以建6个触发器，分别是增加、删除、添加行为前后。

8.视图不支持触发器。

 备注一下：我得MYSQL版本为5.5.40。（查询语句为：select version();）

猎八哥浅谈MYSQL触发器

标签：场景   sql   查看   png   更改   ble   修改   val   charset   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！