Jeter une erreur dans un déclencheur MySQL

Si j’ai un sortinggger before the update à before the update sur une table, comment puis-je générer une erreur qui empêche la mise à jour sur cette table?

Voici un hack qui peut fonctionner. Ce n’est pas propre, mais on dirait que ça pourrait marcher:

Essentiellement, vous essayez simplement de mettre à jour une colonne qui n’existe pas.

Depuis MySQL 5.5, vous pouvez utiliser la syntaxe SIGNAL pour lancer une exception :

 signal sqlstate '45000' set message_text = 'My Error Message'; 

L’état 45000 est un état générique représentant “une exception non gérée définie par l’utilisateur”.


Voici un exemple plus complet de l’approche:

 delimiter // use test// create table sortinggger_test ( id int not null )// drop sortinggger if exists trg_sortinggger_test_ins // create sortinggger trg_sortinggger_test_ins before insert on sortinggger_test for each row begin 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 bad select * from trigger_test; insert into trigger_test values (1); -- succeeds as expected insert into trigger_test values (-1); -- fails as expected select * from trigger_test; 

Malheureusement, la réponse fournie par @RuiDC ne fonctionne pas dans les versions de MySQL antérieures à la version 5.5 car il n’y a pas d’implémentation de SIGNAL pour les procédures stockées.

La solution que j’ai trouvée consiste à simuler un signal table_name doesn't exist une table_name doesn't exist un message d’erreur personnalisé dans le table_name .

Le hack pourrait être implémenté en utilisant des déclencheurs ou en utilisant une procédure stockée. Je décris les deux options ci-dessous en suivant l’exemple utilisé par @RuiDC.

Utiliser des déclencheurs

 DELIMITER $$ -- before inserting new id DROP 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 ; 

Utiliser une procédure stockée

Les procédures stockées vous permettent d'utiliser SQL dynamic, ce qui rend possible l'encapsulation de la fonctionnalité de génération d'erreurs en une seule procédure. Le contrepoint est que nous devrions contrôler les méthodes d'insertion / mise à jour des applications, elles utilisent donc uniquement notre procédure stockée (ne pas accorder de privilèges directs à INSERT / UPDATE).

 DELIMITER $$ -- my_signal procedure CREATE 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 ; 

La procédure suivante est (sur mysql5) un moyen de lancer des erreurs personnalisées et de les connecter simultanément:

 create table mysql_error_generator(error_field varchar(64) unique) engine INNODB; DELIMITER $$ CREATE PROCEDURE throwCustomError(IN errorText VARCHAR(44)) BEGIN DECLARE errorWithDate varchar(64); select concat("[",DATE_FORMAT(now(),"%Y%m%d %T"),"] ", errorText) into errorWithDate; INSERT IGNORE INTO mysql_error_generator(error_field) VALUES (errorWithDate); INSERT INTO mysql_error_generator(error_field) VALUES (errorWithDate); END; $$ DELIMITER ; call throwCustomError("Custom error message with log support."); 
 CREATE TRIGGER sample_sortinggger_msg BEFORE INSERT FOR EACH ROW BEGIN IF(NEW.important_value) < (1*2) THEN DECLARE dummy INT; SELECT Enter your Message Here!!! INTO dummy FROM mytable WHERE mytable.id=new.id END IF; END; 

Une autre méthode (hack) (si vous n’êtes pas sur 5.5+ pour une raison quelconque) que vous pouvez utiliser:

Si vous avez un champ obligatoire, dans un déclencheur, définissez le champ requirejs sur une valeur non valide telle que NULL. Cela fonctionnera pour INSERT et UPDATE. Notez que si NULL est une valeur valide pour le champ requirejs (pour une raison folle), cette approche ne fonctionnera pas.

 BEGIN -- Force one of the following to be assigned otherwise set required field to null which will throw an error IF (NEW.`nullable_field_1` IS NULL AND NEW.`nullable_field_2` IS NULL) THEN SET NEW.`required_id_field`=NULL; END IF; END 

Si vous êtes sur 5.5+, vous pouvez utiliser l’état du signal comme décrit dans d’autres réponses:

 BEGIN -- Force one of the following to be assigned otherwise use signal sqlstate to throw a unique error IF (NEW.`nullable_field_1` IS NULL AND NEW.`nullable_field_2` IS NULL) THEN SIGNAL SQLSTATE '45000' set message_text='A unique identifier for nullable_field_1 OR nullable_field_2 is required!'; END IF; END