Lanzar un error en un disparador de MySQL

votos
136

Si tengo una trigger before the updateen una mesa, ¿cómo puedo lanzar un error que impida la actualización en esa tabla?

Publicado el 01/08/2008 a las 13:12
fuente por usuario
En otros idiomas...                            


6 respuestas

votos
113

En MySQL 5.5, puede utilizar la SIGNALsintaxis para lanzar una excepción :

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

Estado 45000 es un estado genérico que representa "excepción definida por el usuario no controlada".


Aquí está un ejemplo más completo del enfoque:

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;
Respondida el 25/08/2011 a las 12:14
fuente por usuario

votos
52

Aquí hay un truco que puede funcionar. No está limpio, pero parece que podría funcionar:

Básicamente, intenta actualizar una columna que no existe.

Respondida el 01/08/2008 a las 14:02
fuente por usuario

votos
28

Por desgracia, la respuesta proporcionada por @RuiDC no funciona en versiones de MySQL anteriores a la 5.5 porque no hay una aplicación de SEÑAL para procedimientos almacenados.

La solución que he encontrado es para simular una señal de lanzar un table_name doesn't existerror, empujando un mensaje de error personalizado en el table_name.

El truco podría ser implementado usando disparadores o el uso de un procedimiento almacenado. Describo las dos opciones siguientes, siguiendo el ejemplo utilizado por @RuiDC.

El uso de los disparadores

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 ;

El uso de un procedimiento almacenado

Los procedimientos almacenados le permite utilizar SQL dinámico, lo que hace posible la encapsulación de la funcionalidad de generación de error en un procedimiento. El contrapunto es que debemos controlar las aplicaciones insertan métodos / actualización, por lo que el uso exclusivo de procedimiento almacenado (no conceder privilegios directos a 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 ;
Respondida el 28/01/2012 a las 16:46
fuente por usuario

votos
10

El siguiente procedimiento es (en mysql5) una manera de tirar errores personalizados, y registrarlos al mismo tiempo:

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.");
Respondida el 08/11/2012 a las 17:15
fuente por usuario

votos
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;
Respondida el 12/08/2016 a las 15:08
fuente por usuario

votos
4

Otro método (truco) (si no está en 5.5+ por alguna razón) que puede utilizar:

Si usted tiene un campo obligatorio, entonces dentro de un disparador establecer el campo requerido a un valor no válido como NULL. Esto funciona tanto para INSERT IGNORE y UPDATE. Ten en cuenta que si NULL es un valor válido para el campo requerido (por alguna extraña razón), entonces este método no funcionará.

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 usted está en 5.5+ entonces usted puede utilizar el estado de señal tal como se describe en otras respuestas:

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
Respondida el 09/04/2016 a las 20:11
fuente por usuario

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