mysql触发器(Trigger)简明总结和使用实例

一，什么触发器

1，个人理解
触发器，从字面来理解，一触即发的一个器，简称触发器（哈哈，个人理解），举个例子吧，好比天黑了，你开灯了，你看到东西了。你放炮仗，点燃了，一会就炸了。
2，官方定义
触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。
触发器有一个非常好的特性就是:触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。
什么意思，举个例子解释一下，街机游戏大家都玩过吧，闯过一关，闯下一关，有一关没闯过就要从第一关开始。触发器根这个类似。
官方解释如下
触发程序视为单一交易中的一部份，因此可以由原触发程序还原交易，如果在交易过程中侦测到严重的错误（如使用者中断连线），则会自动还原整个交易。
他的作用很明显了，可以保重数据的完整性，下面有一个实例来说明他的好处，以及如果使编写代码不那么复杂

二，触发器语法



代码如下:

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
·         INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
·         UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
·         DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。
请注意，trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。例如，关于INSERT的BEFORE触发程序不仅能被INSERT语句激活，也能被LOAD DATA语句激活。
可能会造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...语法：BEFORE INSERT触发程序对于每一行将激活，后跟AFTER INSERT触发程序，或BEFORE UPDATE和AFTER UPDATE触发程序，具体情况取决于行上是否有重复键。
对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。
trigger_stmt是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN ... END复合语句结构。这样，就能使用存储子程序中允许的相同语句

三，创建解发器

1,用户表user


代码如下:

CREATE TABLE `user` ( 
`id` int(11) NOT NULL auto_increment COMMENT '用户ID', 
`name` varchar(50) NOT NULL default '' COMMENT '名称', 
`sex` int(1) NOT NULL default '0' COMMENT '0为男，1为女', 
PRIMARY KEY  (`id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

INSERT INTO `user` (`id`, `name`, `sex`) VALUES 
(1, '张映', 0), 
(2, 'tank', 0);

2,评论表comment

代码如下:

CREATE TABLE `comment` ( 
`c_id` int(11) NOT NULL auto_increment COMMENT '评论ID', 
`u_id` int(11) NOT NULL COMMENT '用户ID', 
`name` varchar(50) NOT NULL default '' COMMENT '用户名称', 
`content` varchar(1000) NOT NULL default '' COMMENT '评论内容', 
PRIMARY KEY  (`c_id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

INSERT INTO `comment` (`c_id`, `u_id`, `name`, `content`) VALUES 
(1, 1, '张映', '触发器测试'), 
(2, 1, '张映', '解决字段冗余'), 
(3, 2, 'tank', '使代码更简单');

在这里有一个冗余字段name，我们在读取评论进可以用联合查寻来找到user表中的名字，为什么要有冗余字段呢，因简单的sql语句执行效率更高，但不是冗余字段越多越好，冗余字段多了，同样会增加数据库负担 .
我要做的事情是，当我更新user表的name时，触发器同时更新comment表，就不要写php代码去更新了，当用户被删除时，comment表中，有关该用户的数据将被删除

3，更新name触发器

代码如下:

delimiter ||      //mysql 默认结束符号是分号，当你在写触发器或者存储过程时有分号出现，会中止转而执行 
drop trigger if exists updatename||    //删除同名的触发器， 
create trigger updatename after update on user for each row   //建立触发器， 
begin 
//old,new都是代表当前操作的记录行，你把它当成表名，也行; 
if new.name!=old.name then   //当表中用户名称发生变化时,执行 
update comment set comment.name=new.name where comment.u_id=old.id; 
end if; 
end|| 
delimiter ;

4，触发器删除comment数据

代码如下:

delimiter || 
drop trigger if exists deletecomment|| 
create trigger deletecomment before delete on user for each row 
begin 
delete from comment where comment.u_id=old.id; 
end|| 
delimiter ;

有一点很让人郁闷，就是写好的触发器代码，不能修改，你要删除掉重建，郁闷中，对了还有一点就是phpmyadmin，有的能创建触发器，有的不能，有的能创建，但创建了看不到。在研究一下。

5,测试触发器是否可用

a，测试updata触发器


代码如下:

update user set name='苍鹰'  where id = 1;

更新后去comment表里面看看，里面name字段里面的段有没有改变

b，测试delete触发器


代码如下:

delete from user  where id = 1;

更新后去comment表里面看看，里面name字段里面的段有没有改变

四，触发器的优点

1，触发器的"自动性"
对程序员来说，触发器是看不到的，但是他的确做事情了，如果不用触发器的话，你更新了user表的name字段时，你还要写代码去更新其他表里面的冗余字段，我举例子，只是一张表，如果是几张表都有冗余字段呢，你的代码是不是要写很多呢，看上去是不是很不爽呢。
2，触发器的数据完整性
触发器有回滚性，举个例子，我发现我很喜欢举子，就是你要更新五张表的数据，不会出现更新了二个张表，而另外三张表没有更新。
但是如果是用php代码去写的话，就有可能出现这种情况的，比如你更新了二张表的数据，这个时候，数据库挂掉了。你就郁闷了，有的更新了，有的没更新。这样页面显示不一致了，变有bug了。

赞 (0)

相关推荐

  • MySQL通过触发器解决数据库中表的行数限制详解及实例

    MySQL通过触发器解决数据库中表的行数限制详解及实例 最近项目一个需求是对操作日志的数量限制为10万条,超过十万条便删除最旧的那一条,保存数据库中日志数量不超过10万. 当时我的第一想法是通过触发器来做,便在数据库中执行了如下的SQL: delimiter $ create trigger limitLog before insert on OperationLog for each row begin if (select count(*) from OperationLog) > 1000

  • MySQL笔记之触发器的应用

    创建触发器 创建只有一个执行语句的触发器 复制代码 代码如下: CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件ON 表名 FOR EACH ROW 执行语句 其中,触发器名参数指要创建的触发器的名字 BEFORE和AFTER参数指定了触发执行的时间,在事件之前或是之后 FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器 复制代码 代码如下: mysql> CREATE TRIGGER trig1 AFTER INSERT    -> ON

  • MySQL 在触发器里中断记录的插入或更新？

    下面是一种实现的方法.思路就是想办法在触发器中利用一个出错的语句来中断代码的执行. mysql> create table t_control(id int primary key); Query OK, 0 rows affected (0.11 sec) mysql> insert into t_control values (1); Query OK, 1 row affected (0.05 sec) mysql> create table t_bluerosehero(id i

  • Mysql中的触发器简单介绍及使用案例

    什么是触发器? 触发器是数据库的一个程序,他是用来监听着数据表的某个行为,一旦数据表的这个行为发生了,马上执行相应的sql语句 触发器的语法结构: create trigger 触发器的名称触发器事件 on 监听的表名 for each row 行为发生后执行的sql语句 触发器事件组成::两部分组成: 触发器事件发生的时间-----是在监听的表的行为 after before 常用的是after 触发器执行的内容:增删改 创建order 表的时候,需要注意,因为order在mysql中是一个关

  • MySQL触发器使用详解

    MySQL包含对触发器的支持.触发器是一种与表操作有关的数据库对象,当触发器所在表上出现指定事件时,将调用该对象,即表的操作事件触发表上的触发器的执行. 创建触发器 在MySQL中,创建触发器语法如下: 复制代码 代码如下: CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 其中: trigger_name:标识触发器名称,用户自行指定: trigger_tim

  • 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触发器自动更新memcache的实现代码

    mysql 5.1支持触发器以及自定义函数接口(UDF)的特性,如果配合libmemcache以及Memcached Functions for MySQL,就能够实现memcache的自动更新.简单记录一下安装测试步骤. 安装步骤 安装memcached,这个步骤很简单,随处可见 安装mysql server 5.1RC,安装办法也很大众,不废话了 编译libmemcached,解压后安装即可./configure; make; make install 编译Memcached Functio

  • mysql 触发器实现两个表的数据同步

    mysql通过触发器实现两个表的同步 目前,在本地测试成功. 假设本地的两个数据库a和b,a下有表table1(id, val) b下有表table2(id, val) 假设希望当table1中数据更新,table2中数据同步更新. 代码: DELIMITER $$ CREATE /*[DEFINER = { user | CURRENT_USER }]*/ TRIGGER `a`.`触发器名` BEFORE UPDATE ON `a`.`table1` FOR EACH ROW BEGIN I

  • mysql触发器(Trigger)简明总结和使用实例

    一,什么触发器 1,个人理解触发器,从字面来理解,一触即发的一个器,简称触发器(哈哈,个人理解),举个例子吧,好比天黑了,你开灯了,你看到东西了.你放炮仗,点燃了,一会就炸了.2,官方定义触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行.触发器经常用于加强数据的完整性约束和业务规则等. 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数

  • mysql触发器trigger实例详解

    mysql触发器trigger实例详解

    MySQL好像从5.0.2版本就开始支持触发器的功能了,本次博客就来介绍一下触发器,首先还是谈下概念性的东西吧: 什么是触发器 触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合.触发器的这种特性可以协助应用在数据库端确保数据的完整性. 举个例子,比如你现在有两个表[用户表]和[日志表],当一个用户被创建的时候,就需要在日志表中插入创建的log日志,如果在不使用触发器的情况下,你需要编写程序语言逻辑才能实现,但是如果你定义了一个触发器,触发器的作用就是当你在用户表中

  • MySQL触发器trigger的使用

    MySQL触发器trigger的使用

    目录 一.触发器简介 1.什么是触发器? 2.触发器的特性 二..创建触发器 1.创建只有一个执行语句的触发器 2.创建有多个执行语句的触发器 3.NEW与OLD详解 三.查看触发器 1.SHOW TRIGGERS语句查看触发器信息 2.在information_schema.triggers表中查看触发器信息 四.删除触发器 一.触发器简介 1.什么是触发器? 触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合. 2.触发器的特性 有begin end体,begi

  • 详解mysql触发器trigger实例

    详解mysql触发器trigger实例

    目录 什么是触发器 创建触发器 现在有表如下: 这里再啰嗦几句: 限制和注意事项 总结 数据库触发器有以下的作用: MySQL好像从5.0.2版本就开始支持触发器的功能了,本次博客就来介绍一下触发器,首先还是谈下概念性的东西吧: 什么是触发器 触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合.触发器的这种特性可以协助应用在数据库端确保数据的完整性. 举个例子,比如你现在有两个表[用户表]和[日志表],当一个用户被创建的时候,就需要在日志表中插入创建的log日志,如

  • MySQL触发器的使用场景及方法实例

    触发器: 触发器的使用场景以及相应版本: 触发器可以使用的MySQL版本: 版本:MySQL5以上 使用场景例子: 每当增加一个顾客到某个数据库表时,都检查其电话号码格式是否正确,州的缩写是否为大写 每当订购一个产品时,都从库存数量中减去订购的数量 无论何时删除一行,都在某个存档表中保留一个副本 即:在某个表发生更改时自动处理. 如遇到触发器报错"Not allowed to return a result set from a trigger":请划到最后看详解: 触发器的使用: 创

  • MySQL 触发器详解及简单实例

    MySQL 触发器简单实例 语法 CREATE TRIGGER <触发器名称>  --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. { BEFORE | AFTER }  --触发器有执行的时间设置:可以设置为事件发生前或后. { INSERT | UPDATE | DELETE }  --同样也能设定触发的事件:它们可以在执行insert.update或delete的过程中触发. ON <表名称>  --触发器是属于某一个表

  • MySQL 触发器定义与用法简单实例

    本文实例讲述了MySQL 触发器定义与用法.分享给大家供大家参考,具体如下: 语法 CREATE TRIGGER 触发器名称  --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. { BEFORE | AFTER }  --触发器有执行的时间设置:可以设置为事件发生前或后. { INSERT | UPDATE | DELETE }  --同样也能设定触发的事件:它们可以在执行insert.update或delete的过程中触发. ON 表名称

  • mysql触发器原理与用法实例分析

    本文实例讲述了mysql触发器原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是触发器 创建触发器 单条触发器语句 多条触发器语句 查看触发器 删除触发器 触发器的新旧记录引用 首发日期:2018-04-14 什么是触发器: 触发器用来在某些操作之后,"自动"执行一些操作.(比如插入了新的学生信息,那么在班级表中应该修改学生数). 当insert delete update设置触发器之后,执行insert delete update操作就会自动触发设置的内容. 一张表最大

  • MySQL触发器运用于迁移和同步数据的实例教程

    1.迁移数据 进行数据库移植,SQL Server=>MySQL.SQL Server上有如下的Trigger SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER TRIGGER [trg_risks] ON dbo.projectrisk FOR INSERT, UPDATE AS BEGIN UPDATE projectrisk SET classification = case when calc>= 9 then 3 when

  • mysql 触发器用法实例详解

     MySQL触发器语法详解: 触发器 trigger是一种特殊的存储过程,他在插入(inset).删除(delete)或修改(update)特定表中的数据时触发执行,它比数据本身标准的功能更精细和更复杂的数据控制能力.触发器不是由程序调用,而是由某个事件来触发的.在有数据修改时自动强制执行其业务规则,经常用于加强数据的完整性约束和业务规则等.触发器可以查询其他表,而且包含复制的sql语句.触发器也可用于强制引用完整性.触发器可以强制比用check约束定义的约束更为复杂的约束. (一).CREAT

随机推荐