MySQL. Stopping SQL Query Execution with a Trigger
How do you stop SQL execution after a trigger? In other words, under certain conditions defined in the trigger, you need to stop the SQL statement that fired that trigger.
I thought about this and Googled it, but the only thing I managed to come up with was to execute invalid SQL code, thereby stopping the execution of the SQL code that would run after the trigger.
The task was this: customers have statuses, but if the new status (the one being set) is lower than the current one, the query needs to be stopped.
I didn’t want to pile up checks on the scripting-language side, since this behavior needed to be global, and I really didn’t feel like rewriting the wonder-code I’d inherited.
Well, that’s enough talk; now let me show you the implementation:
delimiter //
DROP TRIGGER IF EXISTS `customers_update_status`//
CREATE TRIGGER `customers_update_status` BEFORE UPDATE ON `customers` FOR EACH ROW BEGIN
IF new.`status` < old.`status` THEN
BEGIN
INSERT INTO `customers` SET old.`status` = "STOP_SQL_QUERY";
END;
END IF;
END//
delimiter ;
The status field is of type ENUM, and it has no STOP_SQL_QUERY value, so an error occurs and the SQL query following the trigger is not executed. This isn’t a proper solution, but it’s the only one I’ve found. Actually, it would probably be even better to use UPDATE rather than INSERT. Then the error would look more appropriate.
P.S. Incidentally, the comments on the official CREATE TRIGGER page suggest this very method for stopping the execution of an SQL query.
Comments are closed. Read the original discussion in Russian →
