Throw an error preventing a table update in a MySQL trigger
As of MySQL 5.5, you can use the SIGNAL
syntax to throw an exception:
signal sqlstate '45000' set message_text = 'My Error Message';
State 45000 is a generic state representing "unhandled user-defined exception".
Here is a more complete example of the approach:
delimiter //use test//create table trigger_test( id int not null)//drop trigger if exists trg_trigger_test_ins //create trigger trg_trigger_test_ins before insert on trigger_testfor each rowbegin declare msg varchar(128); if new.id < 0 then set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ', cast(new.id as char)); signal sqlstate '45000' set message_text = msg; end if;end//delimiter ;-- run the following as seperate statements:insert into trigger_test values (1), (-1), (2); -- everything fails as one row is badselect * from trigger_test;insert into trigger_test values (1); -- succeeds as expectedinsert into trigger_test values (-1); -- fails as expectedselect * from trigger_test;
Here is one hack that may work. It isn't clean, but it looks like it might work:
Essentially, you just try to update a column that doesn't exist.
Unfortunately, the answer provided by @RuiDC does not work in MySQL versions prior to 5.5 because there is no implementation of SIGNAL for stored procedures.
The solution I've found is to simulate a signal throwing a table_name doesn't exist
error, pushing a customized error message into the table_name
.
The hack could be implemented using triggers or using a stored procedure. I describe both options below following the example used by @RuiDC.
Using triggers
DELIMITER $$-- before inserting new idDROP 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 ;
Using a stored procedure
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).
DELIMITER $$-- my_signal procedureCREATE 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 ;