mysql trigger 使用以及与 sqlite3 trigger 比较

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

        使用情景，表里的某行数据发生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对象更新即可。

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