TRIGGERs that cause INSERTs to fail? Possible?

Question

In cleaning up this answer I learnt a bit about TRIGGERs and stored procedures in MySQL, but was stunned that, while BEFORE INSERT and BEFORE UPDATE triggers could modify data, they seemingly couldn't cause the insert/update to fail (ie. validation). In this particular case I was able to get this to work by manipulating the data in such a way as to cause a primary key duplicate, which in this particular case made sense, but doesn't necessarily make sense in a general sense.

Is this sort of functionality possible in MySQL? In any other RDBMS (my experience is limited to MySQL sadly)? Perhaps a THROW EXCEPTION style syntax?

from MySQL 5.5, possibly earlier, you can use signals, [see my answer here][1] [1]: http://stackoverflow.com/questions/24/throw-error-in-mysql-trigger/7189396#7189396 — RuiDC, Aug 25 '11 at 11:18

score 19 · Accepted Answer · edited May 15 '19 at 01:16

From this blog post

MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a trigger? On EfNet’s #mysql someone asked:

How do I make a trigger abort the operation if my business rule fails?

In MySQL 5.0 and 5.1 you need to resort to some trickery to make a trigger fail and deliver a meaningful error message. The MySQL Stored Procedure FAQ says this about error handling:

SP 11. Do SPs have a “raise” statement to “raise application errors”? Sorry, not at present. The SQL standard SIGNAL and RESIGNAL statements are on the TODO.

Perhaps MySQL 5.2 will include SIGNAL statement which will make this hack stolen straight from MySQL Stored Procedure Programming obsolete. What is the hack? You’re going to force MySQL to attempt to use a column that does not exist. Ugly? Yes. Does it work? Sure.
CREATE TRIGGER mytabletriggerexample
BEFORE INSERT
FOR EACH ROW BEGIN
IF(NEW.important_value) < (fancy * dancy * calculation) THEN
    DECLARE dummy INT;

    SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
END IF; END;

I actually laughed reading this example... That's an awesome hack that plays on MySQL's error reporting... Here's hoping for SIGNAL sometime soon. — Matthew Scharley, Oct 23 '08 at 14:02
That is so awesome. I had been setting some NOT NULL column to NULL, but this solution throws a meaningful error message. +1! — Bill Karwin, Oct 23 '08 at 16:17
This fails for me when the declare statement is inside the condition. The docs say, [DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.](http://dev.mysql.com/doc/refman/5.0/en/declare.html). — Ian Mackinnon, Jul 07 '12 at 14:19

score 8 · Answer 2 · edited Mar 02 '11 at 12:35

Here is the way I did it. Note the SET NEW='some error';. MySQL will tell you "Variable 'new' can't be set to the value of 'Error: Cannot delete this item. There are records in the sales table with this item.'"

You can trap this in your code and then show the resulting error :)

DELIMITER $$
DROP TRIGGER IF EXISTS before_tblinventoryexceptionreasons_delete $$
CREATE TRIGGER before_tblinventoryexceptionreasons_delete
BEFORE DELETE ON tblinventoryexceptionreasons
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblinventoryexceptionreasons = old.idtblinventoryexceptionreasons) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory exception reasons table with this item.';
  END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP TRIGGER IF EXISTS before_storesalesconfig_delete $$
CREATE TRIGGER before_storesalesconfig_delete
BEFORE DELETE ON tblstoresalesconfig
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblstoresales WHERE tblstoresales.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the sales table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinventory WHERE tblinventory.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory exceptions table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
  END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP TRIGGER IF EXISTS before_tblinvoice_delete $$
CREATE TRIGGER before_tblinvoice_delete
BEFORE DELETE ON tblinvoice
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblinvoice = old.idtblinvoice) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
  END IF;
END$$
DELIMITER ;

score 6 · Answer 3 · answered Sep 30 '14 at 07:34

Because this article comes up towards the top when I search for error handling in MySQL triggers, I thought I'd share some knowledge.

If there is an error, you can force MySQL to use a SIGNAL, but if you don't specify it as a class as SQLEXCEPTION, then nothing will happen, as not all SQLSTATEs are considered bad, and even then you'd have to make sure to RESIGNAL if you have any nested BEGIN/END blocks.

Alternatively, and probably simpler still, within your trigger, declare an exit handler and resignal the exception.

CREATE TRIGGER `my_table_AINS` AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        RESIGNAL;
    DECLARE EXIT HANDLER FOR SQLWARNING
        RESIGNAL;
    DECLARE EXIT HANDLER FOR NOT FOUND
        RESIGNAL; 
    -- Do the work of the trigger.
END

And if in your body there occurs an error, it will be thrown back up to the top and exit with an error. This can also be used in stored procedures and whatnot.

This works with anything version 5.5+.

Upvoting this because it's correct, but unfortunately not all of us have the luxury of working with 5.5+ either. Oh well. — Matthew Scharley, Oct 05 '14 at 23:23

score 1 · Answer 4 · answered Jul 03 '12 at 20:25

Doen't work in triggers (Dynamic SQL is not allowed in stored function or trigger)

I use a very dirty solution:

If NEW.test=1 then CALL TEST_CANNOT_BE_SET_TO_1; end if;

When test=1 Mysql throws the following exception:

PROCEDURE administratie.TEST_CANNOT_BE_SET_TO_1 does not exist

Not sophisticated but fast and usefull.

score 1 · Answer 5 · answered Oct 23 '08 at 13:59

This will abort your INSERT by raising an exception (from http://www.experts-exchange.com/Database/MySQL/Q_23788965.html)

DROP PROCEDURE IF EXISTS `MyRaiseError`$$

CREATE PROCEDURE `MyRaiseError`(msg VARCHAR(62))
BEGIN
DECLARE Tmsg VARCHAR(80);
SET Tmsg = msg;
IF (CHAR_LENGTH(TRIM(Tmsg)) = 0 OR Tmsg IS NULL) THEN
SET Tmsg = 'ERROR GENERADO';
END IF;
SET Tmsg = CONCAT('@@MyError', Tmsg, '@@MyError');
SET @MyError = CONCAT('INSERT INTO', Tmsg);
PREPARE stmt FROM @MyError;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

Usage:

call MyRaiseError('Here error message!');

score -1 · Answer 6 · answered Oct 23 '08 at 14:01

-1

in MS SQL you could make it work using proper syntax:

IF UPDATE(column_name)
BEGIN
  RAISEERROR
  ROLLBACK TRAN
  RETURN
END

http://msdn.microsoft.com/en-us/magazine/cc164047.aspx

answered Oct 23 '08 at 14:01

Ilya Kochetov

17,988
6
44
60

TRIGGERs that cause INSERTs to fail? Possible?

6 Answers6

Linked