ТРИГГЕРЫ, которые приводят к сбою INSERT? Возможный?

Из этого Сообщение блога

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;

Это прервет ваш INSERT, вызвав исключение (из 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$$

Использование:

call MyRaiseError('Here error message!');

в MS SQL вы можете заставить его работать, используя правильный синтаксис:

IF UPDATE(column_name)
BEGIN
  RAISEERROR
  ROLLBACK TRAN
  RETURN
END

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

Вот как я это сделал. Обратите внимание на SET NEW='some error';. MySQL сообщит вам, что «переменной 'new' не может быть присвоено значение 'Ошибка: невозможно удалить этот элемент. В таблице продаж есть записи с этим элементом'».

Вы можете перехватить это в своем коде, а затем показать полученную ошибку :)

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 ;

Не работают в триггерах (Динамический SQL не разрешен в сохраненной функции или триггере)

Я использую очень грязный раствор:

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

Когда test = 1, Mysql выдает следующее исключение:

PROCEDURE administratie.TEST_CANNOT_BE_SET_TO_1 не существует

Не изощренно, но быстро и полезно.

Поскольку эта статья оказывается наверху, когда я ищу обработку ошибок в триггерах MySQL, я решил поделиться некоторыми знаниями.

Если есть ошибка, вы можете заставить MySQL использовать СИГНАЛ, но если вы не укажете его как класс как SQLEXCEPTION, тогда ничего не произойдет, так как не все SQLSTATE считаются плохими, и даже тогда вам придется обязательно RESIGNAL, если у вас есть вложенные блоки BEGIN / END.

В качестве альтернативы, что, вероятно, еще проще, в вашем триггере объявите обработчик выхода и откажитесь от исключения.

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

И если в вашем теле произойдет ошибка, он будет отброшен вверх и выйдет с ошибкой. Это также можно использовать в хранимых процедурах и т. д.

Это работает с любой версией 5.5+.