StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql Before Insert Trigger SET fails
text
Body
copied!<p>I have fairly simple basic table which I will populate with CHAR(4) primary keys.</p> <p>These values are selected from a <strong>chars</strong> table with a subquery.</p> <p>I want to set up a <strong>BEFORE INSERT</strong> query so that each time I <strong>INSERT</strong> a new record from PHP, the <strong>id</strong> field should get the generated CHAR(4) from subquery. </p> <p>"ASDF","This is body text", "1970-01-01 01:01:01"</p> <p>so on. </p> <p>I'm pulling my hair out with this trigger. </p> <pre><code>DELIMETER | CREATE TRIGGER messages_newid BEFORE INSERT ON messages FOR EACH ROW BEGIN SET NEW.id = (SELECT CONCAT(a.val,b.val,c.val,d.val) FROM chars AS a JOIN chars AS b JOIN chars AS c JOIN chars AS d ORDER BY RAND() LIMIT 1); END | </code></pre> <p>Here follows the table structure of <strong>messages</strong></p> <pre><code>CREATE TABLE IF NOT EXISTS `messages` ( `id` varchar(4) collate utf8_unicode_ci NOT NULL, `body` text collate utf8_unicode_ci, `created` datetime default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; </code></pre> <p>And the trick <strong>chars</strong> table I have found within another answer, which is used for CHAR(4) randomness</p> <pre><code>CREATE TABLE IF NOT EXISTS `chars` ( `val` char(1) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; </code></pre> <p>What does this have to do with privileges?</p> <p><strong>EDIT</strong> Here's the new error code </p> <pre><code>#1227 - Access denied; you need the SUPER privilege for this operation </code></pre>

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