database - Throw an error in a MySQL trigger


Translate

If I have a trigger before the update on a table, how can I throw an error that prevents the update on that table?


All Answers
  • Translate

    Here is one hack that may work. It isn't clean, but it looks like it might work:

    Essentially, you just try to update a column that doesn't exist.


  • Translate

    As of MySQL 5.5, you can use the SIGNAL syntax to throw an exception:

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

    State 45000 is a generic state representing "unhandled user-defined exception".


    Here is a more complete example of the approach:

    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;
    

  • Translate

    Unfortunately, the answer provided by @RuiDC does not work in MySQL versions prior to 5.5 because there is no implementation of SIGNAL for stored procedures.

    The solution I've found is to simulate a signal throwing a table_name doesn't exist error, pushing a customized error message into the table_name.

    The hack could be implemented using triggers or using a stored procedure. I describe both options below following the example used by @RuiDC.

    Using triggers

    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 ;
    

    Using a stored procedure

    Stored procedures allows you to use dynamic sql, which makes possible the encapsulation of the error generation functionality in one procedure. The counterpoint is that we should control the applications insert/update methods, so they use only our stored procedure (not granting direct privileges to 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 ;
    

  • Translate

    The following procedure is (on mysql5) a way to throw custom errors , and log them at the same time:

    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.");
    

  • Translate
    CREATE TRIGGER sample_trigger_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;
    

  • Translate

    Another (hack) method (if you are not on 5.5+ for some reason) that you can use:

    If you have a required field, then within a trigger set the required field to an invalid value such as NULL. This will work for both INSERT and UPDATE. Do note that if NULL is a valid value for the required field (for some crazy reason) then this approach will not work.

    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
    

    If you are on 5.5+ then you can use the signal state as described in other answers:

    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