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)
The first five tables use InnoDB engine.
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).
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$$
...as it will be executed before inserts on *_reference tables will be done.
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;
to trigger the trigger before the transaction ends. Bonus: if your video information is updated, the trigger runs again =D.