mysql trigger 使用以及与 sqlite3 trigger 比较

northsky365

已于 2022-12-08 18:25:02 修改

阅读量329

点赞数

分类专栏： MySQL 文章标签：数据库

于 2022-12-08 17:58:39 首次发布

本文链接：https://blog.csdn.net/northsky365/article/details/128234410

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、触发事件的表与触发更新的表使用同一个表

使用情景，表里的某行数据发生update时自动更新修改时间(updated)

sqlite3 3.40.0

MariaDB 10.10.2 对应 MySQL 8

1、sqlite3 update 触发事件

#创建表
CREATE TABLE muser (
    id      INTEGER       PRIMARY KEY
                          UNIQUE
                          NOT NULL,
    name    VARCHAR (128) NOT NULL,
    passwd  VARCHAR (64)  NOT NULL,
    created TIMESTAMP     DEFAULT (datetime('now', 'localtime') ),
    updated TIMESTAMP
);

#创建触发器
CREATE TRIGGER muserUpdated AFTER UPDATE OF status ON muser 
BEGIN 
    UPDATE muser SET updated = datetime('now', 'localtime') WHERE id = OLD.id; 
END;
#[14:53:40] 在提交数据时发生错误：too many levels of trigger recursion

#正确
CREATE TRIGGER muserUpdateTrigger
         AFTER UPDATE OF name,
                         passwd,
                         created
            ON muser
      FOR EACH ROW
BEGIN
    UPDATE muser
       SET updated = datetime('now', 'localtime') 
     WHERE id = OLD.id;
END;

由于触发器触发事件的表与触发更新的表使用了同一个表。触发更新同一表时，不应该使用update语句更新，应该使用set修改NEW对象更新即可。

2、MySQL update 触发事件

#创建数据表
CREATE TABLE `muser` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'user id',
	`name` VARCHAR(128) NOT NULL COMMENT 'user name' COLLATE 'utf8mb4_general_ci',
	`passwd` VARCHAR(64) NOT NULL COMMENT 'pass word' COLLATE 'utf8mb4_general_ci',
	`created` TIMESTAMP NULL DEFAULT current_timestamp() COMMENT 'create time',
	`updated` TIMESTAMP NULL DEFAULT NULL COMMENT 'update time',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='user infor'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

#创建触发器
CREATE DEFINER=`root`@`%` TRIGGER `muserUpdateTrigger` AFTER UPDATE ON `muser` FOR EACH ROW 
BEGIN
	UPDATE test.muser SET updated = CURRENT_TIMESTAMP() WHERE id = OLD.id;
END;
#SQL错误（1442）：Can't ' update table 'muser' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

#正确
CREATE DEFINER=`root`@`%` TRIGGER `muserUpdateTrigger` BEFORE UPDATE ON `muser` FOR EACH ROW BEGIN
		SET NEW.updated = CURRENT_TIMESTAMP() ;
END

由于触发器触发事件的表与触发更新的表使用了同一个表。

触发更新同一表时，Sqlite3 需要申明具体哪些字段update之后再更新对象；MySQL不应该使用update语句更新，应该在update 前（BEFORE）使用set修改NEW对象更新即可。

这种情况下要注意，触发器无限递归。