¿Disparadores que hacen que los INSERT fallen? ¿Posible?

Al limpiar esta respuesta , aprendí un poco acerca de TRIGGER y los procedimientos almacenados en MySQL, pero me asombró que, aunque los disparadores BEFORE INSERT y BEFORE UPDATE podrían modificar los datos, al parecer 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 syntax de estilo THROW EXCEPTION ?

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 statement “subir” para “boost 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; 

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 ; 

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 nesteds.

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+.

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!'); 

No funciona en desencadenadores (no se permite SQL dynamic 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.

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

 IF UPDATE(column_name) BEGIN RAISEERROR ROLLBACK TRAN RETURN END 

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