mysql触发器

2018-06-17 23:45:21来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

mysql手册

一、触发器

1、触发器(trigger)是一种与表操作有关的数据库对象,该对象与编程语言中的函数非常类似,需要先声明后执行,并由事件来触发执行。

2、触发器结构组成

CREATE TRIGGER trigger_name #触发器名
trigger_time #触发时机,取值为 BEFORE 或 AFTER
trigger_event #触发事件,取值为 INSERT、UPDATE 或 DELETE
ON tbl_name #建立触发器的表名,即在哪张表上建立触发器 FOR EACH ROW trigger_stmt #触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句

  注意:1)trigger_event中的INSERT包括INSERT、LOAD DATA(用户高速的向数据库中做批量mysql导入数据load data infile用法)和REPLACE(向表中插入多条记录,如:replace into table (id,name) values('1','aa'),('2','bb'))语句,DELETE包括DELETE和REPLACE语句

     2)对于某一表,不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序,或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序

     3)tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表(临时表)或视图关联起来

     4)触发程序(trigger_stmt中)不能调用将数据返回客户端的存储程序(如SELECT查询),也不能使用采用CALL语句的动态SQL(允许存储程序通过参数将数据返回触发程序)。触发程序也不能使用以显式或隐式方式开始或结束事务的语句,如START TRANSACTION、COMMIT或ROLLBACK。

3、作用

  请参考mysql触发器的作用及语法

二、触发器操作

测试用表

CREATE TABLE `t_dept` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1
CREATE TABLE `t_diary` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `createtime` datetime NOT NULL,
  `dept` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

1、创建

CREATE TRIGGER tri_dept#触发器名 
BEFORE INSERT #触发时间(有BEFORE、AFTER)、事件(有INSERT、DELETE、UPDATE)
ON t_dept FOR EACH ROW #触发器作用的表
BEGIN #激活触发器后执行的语句,可以是多条,以";"分开
INSERT INTO t_diary VALUES (NULL, NOW(), new.dname); #new.dname为触发事件的列
END

  触发器实现在向t_dept表中插入数据前先向t_diary中插入一条数据

CREATE TRIGGER tri_dept_add
AFTER INSERT 
ON t_dept FOR EACH ROW
BEGIN
UPDATE t_diary SET dept = concat(dept ,new.dname) WHERE id = 32;
END

  注意:使用OLD和NEW关键字,能够访问受触发程序影响的行中的列(OLD和NEW不区分大小写)。在INSERT触发程序中,仅能使用NEW.col_name,没有旧行。在DELETE触发程序中,仅能使用OLD.col_name,没有新行。在UPDATE触发程序中,可以使用OLD.col_name来引用更新前的某一行的列,也能使用NEW.col_name来引用更新后的行中的列。

  用OLD命名的列是只读的。你可以引用它,但不能更改它。对于用NEW命名的列,如果具有SELECT权限,可引用它。在BEFORE触发程序中,如果你具有UPDATE权限,可使用“SET NEW.col_name = value”更改它的值。这意味着,你可以使用触发程序来更改将要插入到新行中的值,或用于更新行的值。

  在BEFORE触发程序中,AUTO_INCREMENT列的NEW值为0,不是实际插入新记录时将自动生成的序列号。

  另外:在mysql有些版本使用上述语句在begin end地方报错,无法执行,可以使用“分隔符”解决,代码如下

DELIMITER |
CREATE TRIGGER tri_dept_add
AFTER INSERT 
ON t_dept FOR EACH ROW
BEGIN
UPDATE t_diary SET dept = concat(dept ,new.dname) WHERE id = 32;
END
| DELIMITER ;

2、查看

  SHOW TRIGGERS;

3、删除

  DROP TRIGGER 触发器名; #如DROP TRIGGER tri_dept_add;

4、事务

  触发器本身不支持事务操作,文档原话“触发程序不能使用以显式或隐式方式开始或结束事务的语句,如START TRANSACTION、COMMIT或ROLLBACK”,另外又有“对于事务性表,如果触发程序失败(以及由此导致的整个语句的失败),该语句所执行的所有更改将回滚。对于非事务性表,不能执行这类回滚,因而,即使语句失败,失败之前所作的任何更改依然有效”,即触发器是否有事务取决于原生语句。

  创建如下触发器,当触发器语句报错后将回滚所有执行语句

CREATE TRIGGER tri_dept_add
AFTER INSERT 
ON t_dept FOR EACH ROW
BEGIN
    insert t_diary SET id = 1,dept = new.dname;
END;

5、最后

  在BEGIN块中,还能使用存储子程序中允许的其他语法,如条件和循环等

DROP TRIGGER IF EXISTS upd_check;
DELIMITER |
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN
         SET NEW.amount = 0;
     ELSEIF NEW.amount > 100 THEN
         SET NEW.amount = 100;
     END IF;
END;
| DELIMITER;

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:esclipse连接mysql数据库

下一篇:mysql数据库小常识