mysql - 无法更新存储函数/触发器中的表 'transaction'，因为它已被调用此存储函数/触发器的语句使用

Question

我的触发器有问题。

DELIMITER $$
CREATE TRIGGER complete_transation
AFTER INSERT ON transaction
FOR EACH ROW BEGIN

DECLARE id_trans INT;
DECLARE id_stock INT;
DECLARE type_transaction BIT(1);
DECLARE id_match INT;

SET @id_trans = NEW.idTransaction;
SET @id_stock = NEW.stock_idStocks;
SET @type_transaction = NEW.type;

IF(@type_transaction = 1) THEN
    SET @id_match = (SELECT idTransaction FROM transaction WHERE stock_idStocks = @id_stock AND type = 0);
    UPDATE transaction SET status = 1 WHERE idTransaction = @id_match;
END IF;
IF(@type_transaction = 0) THEN
    SET @id_match = (SELECT idTransaction FROM transaction WHERE stock_idStocks = @id_stock AND type = 1);
    UPDATE transaction SET status = 1 WHERE idTransaction = @id_match;
END IF;



END$$
DELIMITER ;

是否有机会更改表中调用触发器的其他记录？

Answer 1

在存储函数或触发器中，不允许修改已由调用函数或触发器的语句使用(用于读取或写入)的表。当触发器触发时，它可能会锁定表。