mysql之触发器trigger的使用例子

为什么要使用触发器

 

触发器的优点

 

1，触发器的"自动性"

对程序员来说，触发器是看不到的，但是他的确做事情了，如果不用触发器的话，你更新了user表的name字段时，你还要写代码去更新其他表里面的冗余字段，我举例子，只是一张表，如果是几张表都有冗余字段呢，你的代码是不是要写很多呢，看上去是不是很不爽呢。

2，触发器的数据完整性

触发器有回滚性，举个例子，我发现我很喜欢举子，就是你要更新五张表的数据，不会出现更新了二个张表，而另外三张表没有更新。

但是如果是用php代码去写的话，就有可能出现这种情况的，比如你更新了二张表的数据，这个时候，数据库挂掉了。你就郁闷了，有的更新了，有的没更新。这样页面显示不一致了，变有bug了。

 

关于触发器(trigger)的例子

 

触发器(trigger)：监视某种情况，并触发某种操作。

触发器创建语法四要素：1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件(insert/update/delete)

语法：

create trigger triggerName

 

after/before insert/update/delete on 表名

 

for each row   #这句话在mysql是固定的

 

begin

 

sql语句;

 

end;

 

注：各自颜色对应上面的四要素。

首先我们来创建两张表：

#商品表
create table g
 
(
 
　　id int primary key auto_increment,
 
　　name varchar(20),
 
　　num int
 
);
 
#订单表

create table o
 
(
 
　　oid int primary key auto_increment,
 
 　 gid int,
 
     much int
 
);
 
insert into g(name,num) values('商品1',10),('商品2',10),('商品3',10);


 


如果我们在没使用触发器之前：假设我们现在卖了3个商品1，我们需要做两件事


1.往订单表插入一条记录

 

insert into o(gid,much) values(1,3);

 

2.更新商品表商品1的剩余数量

 

update g set num=num-3 where id=1;


 


现在，我们来创建一个触发器：


需要先执行该语句：delimiter $(意思是告诉mysql语句的结尾换成以$结束)


create trigger tg1

after insert on o

for each row 

begin

update g set num=num-3 where id=1;

end$

 

这时候我们只要执行：

 

insert into o(gid,much) values(1,3)$


 


会发现商品1的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。


 


但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。比如：我们往订单表再插入一条记录：insert into o(gid,much) values(2,3),执行完后会发现商品1的数量变4了，而商品2的数量没变，这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。


我们如何在触发器引用行的值，也就是说我们要得到我们新插入的订单记录中的gid或much的值。


对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。


所以现在我们可以这样来改我们的触发器


create trigger tg2

after insert on o

for each row 

begin

update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同)

end$




 


第二个触发器创建完毕，我们先把第一个触发器删掉


drop trigger tg1$


再来测试一下，插入一条订单记录：insert into o(gid,much) values(2,3)$


执行完发现商品2的数量变为7了，现在就对了。


 


现在还存在两种情况：


1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？


2.当用户修改一个订单的数量时，我们触发器修改怎么写?


我们先分析一下第一种情况：


监视地点：o表


监视事件：delete


触发时间：after


触发事件：update


对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。


那我们的触发器就该这样写：


create trigger tg3

 

after delete on o

 

for each row

 

begin

 

update g set num = num + old.much where id = old.gid;(注意这边的变化)

 

end$


 


创建完毕。


再执行delete from o where oid = 2$


会发现商品2的数量又变为10了。


 


第二种情况：


监视地点：o表


监视事件：update


触发时间：after


触发事件：update


对于update而言：被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中的值；


修改的后的数据，用new来表示，new.列名引用被修改之后行中的值。


那我们的触发器就该这样写：


create trigger tg4

 

after update on o

 

for each row

 

begin

 

update g set num = num+old.much-new.much where id = old/new.gid;

 

end$


 


先把旧的数量恢复再减去新的数量就是修改后的数量了。


我们来测试下：先把商品表和订单表的数据都清掉，易于测试。


假设我们往商品表插入三个商品，数量都是10，


买3个商品1：insert into o(gid,much) values(1,3)$


这时候商品1的数量变为7；


我们再修改插入的订单记录: update o set much = 5 where oid = 1$


我们变为买5个商品1，这时候再查询商品表就会发现商品1的数量只剩5了，说明我们的触发器发挥作用了。


如果再看不到我们接着看


在MySQL Server里面也就是对某一个表的一定的操作，触发某种条件（Insert,Update,Delete 等），从而自动执行的一段程序。从这种意义上讲触发器是一个特殊的存储过程。下面通过MySQL触发器实例，来了解一下触发器的工作过程吧！


一、创建MySQL实例数据表：


在mysql的默认的测试test数据库下，创建两个表t_a与t_b：


下载: Create_SQL.sql


/*Table structure for table `t_a` */

DROP TABLE IF EXISTS `t_a`;

CREATE TABLE `t_a` (

  `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,

  `username` varchar(20) DEFAULT NULL,

  `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

 

/*Data for the table `t_a` */

LOCK TABLES `t_a` WRITE;

UNLOCK TABLES;

 

/*Table structure for table `t_b` */

DROP TABLE IF EXISTS `t_b`;

CREATE TABLE `t_b` (

  `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,

  `username` varchar(20) DEFAULT NULL,

  `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1;

 

/*Data for the table `t_b` */

LOCK TABLES `t_b` WRITE;

UNLOCK TABLES;


在t_a表上分创建一个CUD（增、改、删）3个触发器，将t_a的表数据与t_b同步实现CUD，注意创建触发器每个表同类事件有且仅有一个对应触发器，为什么只能对一个触发器，不解释啦，看MYSQL的说明帮助文档吧。


二、创建MySQL实例触发器：


在实例数据表t_a上依次按照下面步骤创建tr_a_insert、tr_a_update、tr_a_delete三个触发器


1、创建INSERT触发器trigger_a_insert：


下载: trigger_a_insert.sql


DELIMITER $$

 

USE `test`$$

 

--判断数据库中是否存在tr_a_insert触发器

DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_insert`$$

--不存在tr_a_insert触发器，开始创建触发器

--Trigger触发条件为insert成功后进行触发

CREATE

    /*!50017 DEFINER = 
'root'@'localhost'
 */

    TRIGGER `tr_a_insert` AFTER INSERT ON `t_a` 

    FOR EACH ROW BEGIN

        --Trigger触发后，同时对t_b新增同步一条数据

        INSERT INTO `t_b` SET username = NEW.username, groupid=NEW.groupid;

    END;

$$

 

DELIMITER;


2、创建UPDATE触发器trigger_a_update：


下载: trigger_a_update.sql


DELIMITER $$

 

USE `test`$$

--判断数据库中是否存在tr_a_update触发器

DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_update`$$

--不存在tr_a_update触发器，开始创建触发器

--Trigger触发条件为update成功后进行触发

CREATE

    /*!50017 DEFINER = 
'root'@'localhost'
 */

    TRIGGER `tr_a_update` AFTER UPDATE ON `t_a` 

    FOR EACH ROW BEGIN 

    --Trigger触发后，当t_a表groupid,username数据有更改时，对t_b表同步一条更新后的数据

      IF new.groupid != old.groupid OR old.username != new.username THEN

        UPDATE `t_b` SET groupid=NEW.groupid,username=NEW.username WHEREusername=OLD.username AND groupid=OLD.groupid;

      END IF;

          

    END;

$$

 

DELIMITER ;


3、创建DELETE触发器trigger_a_delete：


下载: trigger_a_delete.sql


DELIMITER $$

 

USE `test`$$

--判断数据库中是否存在tr_a_delete触发器

DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_delete`$$

--不存在tr_a_delete触发器，开始创建触发器

--Trigger触发条件为delete成功后进行触发

CREATE

    /*!50017 DEFINER = 
'root'@'localhost'
 */

    TRIGGER `tr_a_delete` AFTER DELETE ON `t_a` 

    FOR EACH ROW BEGIN

        --t_a表数据删除后，t_b表关联条件相同的数据也同步删除

        DELETE FROM `t_b` WHERE username=Old.username AND groupid=OLD.groupid;

    END;

$$

 

DELIMITER ;


三、测试MySQL实例触发器：


 


分别测试实现t_a与t_b实现数据同步CUD(增、改、删)3个Triggers


1、测试MySQL的实例tr_a_insert触发器：


在t_a表中新增一条数据，然后分别查询t_a/t_b表的数据是否数据同步，测试触发器成功标志，t_a表无论在何种情况下，新增了一条或多条记录集时，没有t_b表做任何数据insert操作，它同时新增了一样的多条记录集。


下面来进行MySQL触发器实例测试：


--t_a表新增一条记录集

    INSERT INTO `t_a` (username,groupid) VALUES ('sky54.net',123)

   

    --查询t_a表

    SELECT id,username,groupid FROM `t_a`

   

    --查询t_b表

    SELECT id,username,groupid FROM `t_b`




2、测试MySQL的实例tr_a_update、tr_a_delete触发器：


 


这两个MySQL触发器测试原理、步骤与tr_a_insert触发器一样的，先修改/删除一条数据，然后分别查看t_a、t_b表的数据变化情况，数据变化同步说明Trigger实例成功，否则需要逐步排查错误原因。


世界上任何一种事物都其其优点和缺点，优点与缺点是自身一个相对立的面。当然这里不是强调“世界非黑即白”式的“二元论”，“存在即合理”嘛。当然MySQL触发器的优点不说了，说一下不足之处，MySQL Trigger没有很好的调试、管理环境，难于在各种系统环境下测试，测试比MySQL存储过程要难，所以建议在生成环境下，尽量用存储过程来代替MySQL触发器。


 


以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql

                    ，                数据

                    ，                测试

                    ，                实例

                    ，                触发器

                                    事件

                                    mysql 触发器 例子、mysql中触发器的使用、mysql触发器使用、mysql触发器使用案例、mysql触发器的使用，以便于您获取更多的相关知识。
				


				
时间： 2024-09-15 23:07:34

		
		


		


	

	
	

		


		
mysql之触发器trigger的使用例子的相关文章


								

		

			

                mysql中触发器的简单实例
			

		

		

									

				 一个简单的例子 1.1. 创建表:    create table t(s1 integer); 1.2. 触发器:  代码如下 复制代码 ?delimiter | create trigger t_trigger before insert on t  for each row  begin set @x = "hello trigger";        set NEW.s1 = 55; end;  | 1.3. 如果触发器创建错误,可能只能删除了,至少我试过不能replace			

		

	

				

		

			

                Oracle触发器trigger详解_oracle
			

		

		

									

				触发器相关概念及语法 概述 本篇博文中主要探讨以下内容: 什么是触发器 触发器的应用场景 触发器的语法 触发器的类型 案例 数据: 触发器的概念和第一个触发器 数据库触发器是一个与表相关联的,存储的PL/SQL 语句. 每当一个特定的数据操作语句(insert update delete)在指定的表上发出时,Oracle自动执行触发器中定义的语句序列. 举个简单的例子: 当员工表中新增一条记录后,自动打印"成功插入新员工" create or replace trigger inser			

		

	

				

		

			

                Mysql下建立用户授权权限例子
			

		

		

									

				用户授权在mysql中使用grant命令就可以了,我相信各位都会有了解过了,下面小编为各位介绍一个Mysql下建立用户授权权限例子,希望本文章对各位有帮助．     建立用户,授权数据库: mysql> create user 'byrd'@'localhost' identified by 'admin123';    #建立主机为localhost,密码为admin123的用户byrd Query OK, 0 rows affected (0.05 sec) mysql> show gra			

		

	

				

		

			

                mysql-关于Mysql设置触发器以后不能插入的问题
			

		

		

									

				问题描述 关于Mysql设置触发器以后不能插入的问题 我在Mysql中设置了一个触发器,在用户还没有修改用户名的情况下将uid作为默认的用户名: enter code here ///触发器没法自己修改自己的表... DELIMITER | CREATE TRIGGER default_name AFTER INSERT ON user FOR EACH ROW BEGIN update user set NEW.user_name= NEW.uid; END | 然后插入了一条数据,发现出现了			

		

	

				

		

			

                MYSQL设置触发器权限问题的解决方法_Mysql
			

		

		

									

				本文实例讲述了MYSQL设置触发器权限的方法,针对权限错误的情况非常实用.具体分析如下: mysql导入数据提示没有SUPER Privilege权限处理,如下所示: ERROR 1419 (HY000): You do not have the SUPER Privilege and Binary Logging is Enabled 导入function . trigger 到 MySQL database,报错: You do not have the SUPER privilege an			

		

	

				

		

			

                MySQL中触发器的基础学习教程_Mysql
			

		

		

									

				0.触发器的基本概念触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力. 数据库触发器有以下的作用: (1).安全性.可以基于数据库的值使用户具有操作数据库的某种权利.   # 可以基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据.   # 可以基于数据库中的数据限制用户的操作,例如不允许股票的价格的升幅一次超过10%. (2).审计.可以跟踪用户对数据库的操作.     # 审计用户操作数据库的语句.			

		

	

				

		

			

                mysql创建触发器报错，请高手帮忙看下
			

		

		

									

				问题描述 mysql创建触发器报错,请高手帮忙看下 DROP TABLE if EXISTS tb; CREATE TABLE tb(BH CHAR(16),content VARCHAR(20),date DATETIME,val INT); CREATE TRIGGER tri_NewBH BEFORE INSERT ON tb FOR EACH ROW BEGIN DECLARE dt CHAR(8) DECLARE bh_id CHAR(16) DECLARE number INT(1)			

		

	

				

		

			

                MySQL的触发器写法
			

		

		

									

				mysql教程的触发器写法*/ trigger_name:触发器的名字,我常用的命名规则t_name_tablename_(b|a)(i|u|d),t:触发器标识,name:英文名,tablename:表名,b(before):标识是触发事件之前,a(after):标识触发事件之后,i(insert):标识insert事件,u(update):标识update事件,d(delete):标识delete事件; trigger_time:触发时间(before或after) trigger_even			

		

	

				

		

			

                mysql中字符串索引问题与例子
			

		

		

									

				  字符串索引与数字索引有一些方面如果没做好会非常的慢了,今天我们就一起来看看小编整理的一些mysql中字符串索引问题与例子了,因为字符索引相对来说也简单文章就简单的整理了一些例子,希望对各位有帮助. 事情的起因是线上日志发现的mysql慢查询.100万数据量的标准,联合查询全部走索引的情况下,尽然要600多毫秒.很不解,但是将索引列由varchar(50)型改为bigint型后,数据提升了30倍.究其原因就索引树上搜索时要进行大量的比较操作,而字符串的比较比整数的比较耗时的多. 所以建议一般情