<p>If I have a trigger before the update on a table, how can I throw an error that prevents the update on that table?</p>

[{'answer_id': 49, 'author': 'Justin', 'author_id': 43, 'author_profile': 'https://Stackoverflow.com/users/43', 'pm_score': 7, 'selected': True, 'text': '<p><a href="https://web.archive.org/web/20180330233216/http://www.brokenbuild.com/blog/2006/08/15/mysql-triggers-how-do-you-abort-an-insert-update-or-delete-with-a-trigger/" rel="noreferrer">Here is one hack</a> that may work. It isn\'t clean, but it looks like it might work:</p>\n\n<p>Essentially, you just try to update a column that doesn\'t exist.</p>\n'}, {'answer_id': 7189396, 'author': 'RuiDC', 'author_id': 521586, 'author_profile': 'https://Stackoverflow.com/users/521586', 'pm_score': 7, 'selected': False, 'text': '<p>As of MySQL 5.5, you can use the <a href="https://dev.mysql.com/doc/refman/5.7/en/signal.html" rel="noreferrer"><code>SIGNAL</code> syntax to throw an exception</a>:</p>\n\n<pre><code>signal sqlstate \'45000\' set message_text = \'My Error Message\';\n</code></pre>\n\n<p>State 45000 is a generic state representing "unhandled user-defined exception".</p>\n\n<hr>\n\n<p>Here is a more complete example of the approach:</p>\n\n<pre><code>delimiter //\nuse test//\ncreate table trigger_test\n(\n    id int not null\n)//\ndrop trigger if exists trg_trigger_test_ins //\ncreate trigger trg_trigger_test_ins before insert on trigger_test\nfor each row\nbegin\n    declare msg varchar(128);\n    if new.id &lt; 0 then\n        set msg = concat(\'MyTriggerError: Trying to insert a negative value in trigger_test: \', cast(new.id as char));\n        signal sqlstate \'45000\' set message_text = msg;\n    end if;\nend\n//\n\ndelimiter ;\n-- run the following as seperate statements:\ninsert into trigger_test values (1), (-1), (2); -- everything fails as one row is bad\nselect * from trigger_test;\ninsert into trigger_test values (1); -- succeeds as expected\ninsert into trigger_test values (-1); -- fails as expected\nselect * from trigger_test;\n</code></pre>\n'}, {'answer_id': 9046539, 'author': 'el.atomo', 'author_id': 831797, 'author_profile': 'https://Stackoverflow.com/users/831797', 'pm_score': 5, 'selected': False, 'text': '<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>\n\n<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>\n\n<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>\n\n<h2>Using triggers</h2>\n\n\n\n<pre><code>DELIMITER $$\n-- before inserting new id\nDROP TRIGGER IF EXISTS before_insert_id$$\nCREATE TRIGGER before_insert_id\n    BEFORE INSERT ON test FOR EACH ROW\n    BEGIN\n        -- condition to check\n        IF NEW.id &lt; 0 THEN\n            -- hack to solve absence of SIGNAL/prepared statements in triggers\n            UPDATE `Error: invalid_id_test` SET x=1;\n        END IF;\n    END$$\n\nDELIMITER ;\n</code></pre>\n\n<h2>Using a stored procedure</h2>\n\n<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>\n\n\n\n<pre><code>DELIMITER $$\n-- my_signal procedure\nCREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))\nBEGIN\n    SET @sql=CONCAT(\'UPDATE `\', in_errortext, \'` SET x=1\');\n    PREPARE my_signal_stmt FROM @sql;\n    EXECUTE my_signal_stmt;\n    DEALLOCATE PREPARE my_signal_stmt;\nEND$$\n\nCREATE PROCEDURE insert_test(p_id INT)\nBEGIN\n    IF NEW.id &lt; 0 THEN\n         CALL my_signal(\'Error: invalid_id_test; Id must be a positive integer\');\n    ELSE\n        INSERT INTO test (id) VALUES (p_id);\n    END IF;\nEND$$\nDELIMITER ;\n</code></pre>\n'}, {'answer_id': 13293014, 'author': 'Marinos An', 'author_id': 1555615, 'author_profile': 'https://Stackoverflow.com/users/1555615', 'pm_score': 4, 'selected': False, 'text': '<p>The following procedure is (on mysql5) a way to throw custom errors , and log them at the same time:</p>\n\n<pre><code>create table mysql_error_generator(error_field varchar(64) unique) engine INNODB;\nDELIMITER $$\nCREATE PROCEDURE throwCustomError(IN errorText VARCHAR(44))\nBEGIN\n    DECLARE errorWithDate varchar(64);\n    select concat("[",DATE_FORMAT(now(),"%Y%m%d %T"),"] ", errorText) into errorWithDate;\n    INSERT IGNORE INTO mysql_error_generator(error_field) VALUES (errorWithDate);\n    INSERT INTO mysql_error_generator(error_field) VALUES (errorWithDate);\nEND;\n$$\nDELIMITER ;\n\n\ncall throwCustomError("Custom error message with log support.");\n</code></pre>\n'}, {'answer_id': 36523822, 'author': 'PhotonFalcon', 'author_id': 3088965, 'author_profile': 'https://Stackoverflow.com/users/3088965', 'pm_score': 3, 'selected': False, 'text': "<p>Another (hack) method (if you are not on 5.5+ for some reason) that you can use:</p>\n\n<p>If you have a required field, then within a trigger set the required field to an invalid value such as NULL. This will work for both INSERT and UPDATE. Do note that if NULL is a valid value for the required field (for some crazy reason) then this approach will not work.</p>\n\n<pre><code>BEGIN\n    -- Force one of the following to be assigned otherwise set required field to null which will throw an error\n    IF (NEW.`nullable_field_1` IS NULL AND NEW.`nullable_field_2` IS NULL) THEN\n        SET NEW.`required_id_field`=NULL;\n    END IF;\nEND\n</code></pre>\n\n<p>If you are on 5.5+ then you can use the signal state as described in other answers:</p>\n\n<pre><code>BEGIN\n    -- Force one of the following to be assigned otherwise use signal sqlstate to throw a unique error\n    IF (NEW.`nullable_field_1` IS NULL AND NEW.`nullable_field_2` IS NULL) THEN\n        SIGNAL SQLSTATE '45000' set message_text='A unique identifier for nullable_field_1 OR nullable_field_2 is required!';\n    END IF;\nEND\n</code></pre>\n"}, {'answer_id': 38924077, 'author': 'BHUVANESH MOHANKUMAR', 'author_id': 456918, 'author_profile': 'https://Stackoverflow.com/users/456918', 'pm_score': 3, 'selected': False, 'text': '<pre><code>CREATE TRIGGER sample_trigger_msg \n    BEFORE INSERT\nFOR EACH ROW\n    BEGIN\nIF(NEW.important_value) &lt; (1*2) THEN\n    DECLARE dummy INT;\n    SELECT \n           Enter your Message Here!!!\n INTO dummy \n        FROM mytable\n      WHERE mytable.id=new.id\nEND IF;\nEND;\n</code></pre>\n'}, {'answer_id': 62135883, 'author': 'Bashir', 'author_id': 5988526, 'author_profile': 'https://Stackoverflow.com/users/5988526', 'pm_score': 1, 'selected': False, 'text': "<pre><code>DELIMITER @@\nDROP TRIGGER IF EXISTS trigger_name @@\nCREATE TRIGGER trigger_name \nBEFORE UPDATE ON table_name\nFOR EACH ROW\nBEGIN\n\n  --the condition of error is: \n  --if NEW update value of the attribute age = 1 and OLD value was 0\n  --key word OLD and NEW let you distinguish between the old and new value of an attribute\n\n   IF (NEW.state = 1 AND OLD.state = 0) THEN\n       signal sqlstate '-20000' set message_text = 'hey it's an error!';     \n   END IF;\n\nEND @@ \nDELIMITER ;\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/24', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/22/']