MySQL:提交事务后调用触发器 [英] MySQL: Invoke trigger after transaction is committed

问题描述

我有一个非常简单的数据库，具有以下架构:

I've a very simple database with following schema:

video (id, title, description)
category (id, name)
tag (id, name)

video_category_reference (video_id, category_id)
video_tag_reference(video_id, tag_id)

abc_table (video_id, description, categories)

前五个表使用InnoDB引擎.

The first five tables use InnoDB engine.

最后一个表-abc_table使用MyISAM引擎，并且包含某种缓存". description列存储CONCAT(video.title, video.description, GROUP_CONCAT(tag.name))的结果，而categories列存储GROUP_CONCAT(category.id)的结果.

The last table - abc_table uses MyISAM engine and it contains some kind of "cache". description column stores the result of CONCAT(video.title, video.description, GROUP_CONCAT(tag.name)) and categories column stores the result of GROUP_CONCAT(category.id).

我需要的是一个触发器，它将在创建新视频后填充abc_table.始终以相同的方式创建新视频:

What I need is a trigger that will populate abc_table after a new video is created. A new video will be created always the same way:

START TRANSACTION;

INSERT INTO video VALUES(NULL, "My video", "description");

SET @vid = (SELECT LAST_INSERT_ID());

INSERT INTO video_category_reference VALUES (@vid, 1), (@vid, 2), (@vid, 3), (@vid, 4);
INSERT INTO video_tag_reference VALUES (@vid, 5), (@vid, 6), (@vid, 7), (@vid, 8);

COMMIT;

不幸的是，我不能使用此触发器:

Unfortunately I can't use this trigger:

CREATE TRIGGER after_insert_on_video AFTER INSERT ON video FOR EACH ROW BEGIN
    SET @categories = (SELECT GROUP_CONCAT(category_id) FROM video_category_reference WHERE video_id = NEW.id GROUP BY video_id);
    SET @tags = (SELECT GROUP_CONCAT(t.name) FROM video_tag_reference vtr JOIN tag t ON vtr.tag_id = t.id WHERE video_id = NEW.id GROUP BY video_id);

    INSERT INTO video_search_table VALUES (NEW.id, CONCAT(NEW.title, NEW.raw_description, @tags), @categories);
END$$

...因为它将在完成对*_reference表的插入之前执行.

...as it will be executed before inserts on *_reference tables will be done.

有什么办法可以强制MySQL在提交事务后执行触发器?还是我必须为*_referemce表创建触发器以修改abc_table中的值?

Is there any way I could force MySQL to execute a trigger after the transaction is commited? Or do I have to create triggers for *_referemce tables that will modify values in abc_table?

推荐答案

您可以将触发器更改为更新后"，并快速进行操作:

You could change your trigger to AFTER UPDATE and have a quick:

UPDATE video SET id=@vid WHERE id=@vid;

在事务结束之前触发触发器.奖励:如果您的视频信息已更新，则触发器将再次运行= D.

to trigger the trigger before the transaction ends. Bonus: if your video information is updated, the trigger runs again =D.

这篇关于MySQL:提交事务后调用触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！