StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Unfortunately, the answer provided by @RuiDC does not work in MySQL versions prior to 5.5 because there is no implementation of <em>SIGNAL</em> for stored procedures.</p> <p>The solution I've <a href="http://www.devshed.com/c/a/MySQL/Using-the-SIGNAL-Statement-for-Error-Handling/1/" rel="noreferrer">found</a> is to simulate a signal throwing a <code>table_name doesn't exist</code> error, pushing a customized error message into the <code>table_name</code>.</p> <p>The hack could be implemented using triggers or using a stored procedure. I describe both options below following the example used by @RuiDC.</p> <h2>Using triggers</h2> <pre><code>DELIMITER $$ -- before inserting new id DROP TRIGGER IF EXISTS before_insert_id$$ CREATE TRIGGER before_insert_id BEFORE INSERT ON test FOR EACH ROW BEGIN -- condition to check IF NEW.id < 0 THEN -- hack to solve absence of SIGNAL/prepared statements in triggers UPDATE `Error: invalid_id_test` SET x=1; END IF; END$$ DELIMITER ; </code></pre> <h2>Using a stored procedure</h2> <p>Stored procedures allows you to use dynamic sql, which makes possible the encapsulation of the error generation functionality in one procedure. The counterpoint is that we should control the applications insert/update methods, so they use only our stored procedure (not granting direct privileges to INSERT/UPDATE).</p> <pre><code>DELIMITER $$ -- my_signal procedure CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255)) BEGIN SET @sql=CONCAT('UPDATE `', in_errortext, '` SET x=1'); PREPARE my_signal_stmt FROM @sql; EXECUTE my_signal_stmt; DEALLOCATE PREPARE my_signal_stmt; END$$ CREATE PROCEDURE insert_test(p_id INT) BEGIN IF NEW.id < 0 THEN CALL my_signal('Error: invalid_id_test; Id must be a positive integer'); ELSE INSERT INTO test (id) VALUES (p_id); END IF; END$$ DELIMITER ; </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