mysql - update - ¿Disparadores que hacen que los INSERT fallen? ¿Posible?

trigger mysql w3schools

Question

Al limpiar esta respuesta , aprendí un poco acerca de TRIGGER y los procedimientos almacenados en MySQL, pero me asombró que, aunque los desencadenadores BEFORE INSERT y BEFORE UPDATE pudieran modificar los datos, aparentemente no podían hacer que fallara la inserción / actualización (es decir, la validación ) En este caso particular, pude hacer que funcionara manipulando los datos de tal manera que causara una duplicación de clave primaria, que en este caso particular tenía sentido, pero que no necesariamente tiene sentido en un sentido general.

¿Es este tipo de funcionalidad posible en MySQL? En cualquier otro RDBMS (mi experiencia se limita a MySQL tristemente)? Tal vez una sintaxis de estilo THROW EXCEPTION ?

Answer 1

De esta publicación de blog

Disparadores de MySQL: ¿Cómo se aborta un INSERT, UPDATE o DELETE con un trigger? En #missql de EfNet, alguien preguntó:

¿Cómo puedo hacer que un desencadenador anule la operación si falla mi regla de negocio?

En MySQL 5.0 y 5.1 necesita recurrir a algunos trucos para hacer que un desencadenador falle y entregue un mensaje de error significativo. Las preguntas frecuentes sobre el procedimiento almacenado de MySQL dicen esto sobre el manejo de errores:

SP 11. ¿Los SP tienen una declaración "subir" para "aumentar los errores de aplicación"? Lo siento, no en este momento. Las sentencias SIGNAL y RESIGNAL estándar de SQL están en TODO.

Quizás MySQL 5.2 incluirá la instrucción SIGNAL que hará que este truco robado directamente de MySQL Stored Procedure Programming quede obsoleto. ¿Qué es el truco? Va a obligar a MySQL a intentar utilizar una columna que no existe. ¿Feo? Sí. ¿Funciona? Por supuesto.

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;

Answer 2

Esto abortará su INSERT al generar una excepción (de 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$$

Uso:

call MyRaiseError(''Here error message!'');

Answer 3

en MS SQL puede hacer que funcione usando la sintaxis adecuada:

IF UPDATE(column_name)
BEGIN
  RAISEERROR
  ROLLBACK TRAN
  RETURN
END

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

Answer 4

Esta es la forma en que lo hice. Tenga en cuenta SET NEW=''some error''; . MySQL le dirá "Variable ''nueva'' no se puede establecer en el valor de ''Error: no se puede eliminar este elemento. Hay registros en la tabla de ventas con este elemento''".

Puedes atrapar esto en tu código y luego mostrar el error resultante :)

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 ;

Answer 5

No funciona en desencadenadores (no se permite SQL dinámico en función almacenada o desencadenador)

Yo uso una solución muy sucia:

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

Cuando test = 1 Mysql arroja la siguiente excepción:

PROCEDIMIENTO administratie.TEST_CANNOT_BE_SET_TO_1 no existe

No sofisticado pero rápido y útil.

Answer 6

Debido a que este artículo se acerca a la parte superior cuando busco el manejo de errores en los disparadores de MySQL, pensé en compartir algunos conocimientos.

Si hay un error, puede forzar a MySQL a usar una SEÑAL , pero si no lo especifica como una clase como SQLEXCEPTION, entonces no pasará nada, ya que no todos los SQLSTATE se consideran malos, e incluso entonces tendría que asegúrese de RESIGNAL si tiene bloques BEGIN / END anidados.

Alternativamente, y probablemente aún más simple, dentro de su activador, declare un manejador de salida y resigne la excepción.

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

Y si en su cuerpo se produce un error, se lanzará de nuevo a la parte superior y saldrá con un error. Esto también se puede usar en procedimientos almacenados y otras cosas.

Esto funciona con cualquier versión 5.5+.