StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: Invoke trigger after transaction is committed
text
Body
copied!<p>I've a very simple database with following schema:</p> <pre><code>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) </code></pre> <p>The first five tables use InnoDB engine.</p> <p>The last table - <code>abc_table</code> uses MyISAM engine and it contains some kind of "cache". <code>description</code> column stores the result of <code>CONCAT(video.title, video.description, GROUP_CONCAT(tag.name))</code> and <code>categories</code> column stores the result of <code>GROUP_CONCAT(category.id)</code>.</p> <p>What I need is a trigger that will populate <code>abc_table</code> after a new video is created. A new video will be created always the same way:</p> <pre><code>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; </code></pre> <p>Unfortunately I can't use this trigger:</p> <pre><code>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$$ </code></pre> <p>...as it will be executed before inserts on <code>*_reference</code> tables will be done.</p> <hr> <p>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 <code>*_referemce</code> tables that will modify values in <code>abc_table</code>?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload