在 MySQL 触发器中引发错误

Throw an error in a MySQL trigger

提问by Matt MacLean

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

如果我在更新表之前有一个触发器，我如何抛出一个错误来阻止该表的更新？

采纳答案by Justin

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

这里有个hack技巧可能有效。它不干净，但看起来它可能有效：

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

本质上，您只是尝试更新不存在的列。

回答by RuiDC

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

从 MySQL 5.5 开始，您可以使用以下SIGNAL语法抛出异常：

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

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

状态 45000 是表示“未处理的用户定义异常”的通用状态。

---

---

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;

回答by el.atomo

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

不幸的是，@RuiDC 提供的答案在 5.5 之前的 MySQL 版本中不起作用，因为没有为存储过程实现SIGNAL。

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

我找到的解决方案是模拟一个抛出table_name doesn't exist错误的信号，将自定义的错误消息推送到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.

黑客可以使用触发器或使用存储过程来实现。我按照@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).

存储过程允许您使用动态 sql，这使得将错误生成功能封装在一个过程中成为可能。相反，我们应该控制应用程序的插入/更新方法，因此它们只使用我们的存储过程（不授予 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 ;

回答by Marinos An

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

以下过程是（在 mysql5 上）抛出自定义错误并同时记录它们的方法：

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

回答by PhotonFalcon

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

您可以使用的另一种（hack）方法（如果您由于某种原因不在 5.5+ 上）：

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.

如果您有必填字段，则在触发器内将必填字段设置为无效值，例如 NULL。这对 INSERT 和 UPDATE 都有效。请注意，如果 NULL 是必填字段的有效值（出于某种疯狂的原因），那么这种方法将不起作用。

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:

如果您使用的是 5.5+，那么您可以使用其他答案中所述的信号状态：

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

回答by BHUVANESH MOHANKUMAR

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;