Jeter une erreur dans un déclencheur MySQL

voix
136

Si j'ai un trigger before the updatesur une table, comment puis - je jeter une erreur qui empêche la mise à jour sur cette table?

Créé 01/08/2008 à 13:12
source utilisateur
Dans d'autres langues...                            


6 réponses

voix
113

MySQL 5.5, vous pouvez utiliser la SIGNALsyntaxe pour lancer une exception :

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

État 45000 est un état générique qui représente « exception non gérée définie par l'utilisateur ».


Voici un exemple plus complet de l'approche:

delimiter //
use test//
create table trigger_test
(
    id int not null
)//
drop trigger if exists trg_trigger_test_ins //
create trigger trg_trigger_test_ins before insert on trigger_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;
Créé 25/08/2011 à 12:14
source utilisateur

voix
52

Voici une astuce qui peut fonctionner. Il est pas propre, mais il semble que cela pourrait fonctionner:

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

Créé 01/08/2008 à 14:02
source utilisateur

voix
28

Malheureusement, la réponse fournie par @RuiDC ne fonctionne pas dans les versions de MySQL avant 5.5 , car il n'y a pas de mise en œuvre SIGNAL pour les procédures stockées.

La solution que j'ai trouvé est de simuler un signal lancer une table_name doesn't existerreur, poussant un message d'erreur personnalisé dans le table_name.

Le hack peut être mis en œuvre en utilisant des déclencheurs ou à l'aide d'une procédure stockée. Je décris les deux options ci-dessous l'exemple utilisé par @RuiDC.

L'utilisation des déclencheurs

DELIMITER $$
-- before inserting new id
DROP TRIGGER IF EXISTS before_insert_id$$
CREATE TRIGGER before_insert_id
    BEFORE INSERT IGNORE  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 ;

En utilisant une procédure stockée

Les procédures stockées vous permet d'utiliser SQL dynamique, ce qui rend possible l'encapsulation de la fonctionnalité de génération d'erreur dans une procédure. Le contrepoint est que nous devons contrôler les applications des méthodes insérer / mise à jour, de sorte qu'ils utilisent seulement notre procédure stockée (ne pas accorder de privilèges directs à INSERT IGNORE / 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 IGNORE  INTO test (id) VALUES (p_id);
    END IF;
END$$
DELIMITER ;
Créé 28/01/2012 à 16:46
source utilisateur

voix
10

La procédure suivante est (sur mysql5) un moyen de jeter des erreurs personnalisées, et les connecter en même temps:

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  IGNORE INTO mysql_error_generator(error_field) VALUES (errorWithDate);
    INSERT IGNORE  INTO mysql_error_generator(error_field) VALUES (errorWithDate);
END;
$$
DELIMITER ;


call throwCustomError("Custom error message with log support.");
Créé 08/11/2012 à 17:15
source utilisateur

voix
6
CREATE TRIGGER sample_trigger_msg 
    BEFORE INSERT IGNORE 
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;
Créé 12/08/2016 à 18:08
source utilisateur

voix
4

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, puis dans un déclencheur défini le champ nécessaire à une valeur non valide, comme NULL. Cela fonctionne pour les deux INSERT et UPDATE IGNORE. Ne noter que si la valeur NULL est une valeur valide pour le champ requis (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
Créé 09/04/2016 à 23:11
source utilisateur

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more