MySQL 函数错误 1442

Question

CREATE FUNCTION TWO_FRONT(txt CHAR(30))
RETURNS CHAR(2)
RETURN SUBSTRING(txt, 1, 2);

DELIMITER $$
CREATE FUNCTION CHECK_AVABILITY(t_nama CHAR(30))
RETURNS INT(4)
BEGIN
DECLARE vreturn INT(4);
DECLARE P CHAR(8);
SET p = (SELECT MAX(id) FROM tabel_user WHERE nama like CONCAT(TWO_FRONT(t_nama),'%'));
IF ISNULL(p)THEN
    SET vreturn = 0;
ELSE
    SET vreturn = SUBSTRING(p, 4, 4);
END IF;
RETURN vreturn;
END $$
DELIMITER;

CREATE FUNCTION COMBINE(fn CHAR(2), nu CHAR(4))
RETURNS CHAR(7)
RETURN CONCAT(UPPER(fn),'-',nu);

CREATE TRIGGER cast_id AFTER INSERT ON tabel_user 
FOR EACH ROW 
BEGIN 
UPDATE tabel_user SET id = COMBINE(TWO_FRONT(OLD.nama),CHECK_AVABILITY(OLD.nama)) WHERE id = OLD.id;
END;

一切都很好，但是在我插入这个之后......

INSERT INTO tabel_user VALUES('','Blabla');

错误来了...

ERROR 1442 (HY000): Can't update table 'tabel_user' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

你能帮帮我吗？

Answer 1

您正在发送 MySQL 混合消息。

cast_id 在 INSERT 之后触发，但它正在寻找 OLD.nama 和 OLD.id。另一方面，OLD 仅在调用 UPDATE 时才存在。我想你的意思是:

CREATE TRIGGER cast_id AFTER INSERT ON tabel_user 
FOR EACH ROW 
BEGIN 
    -- notice no "UPDATE" or "WHERE", "SET" implies "set on this current row"
    SET NEW.id = COMBINE(TWO_FRONT(NEW.nama),CHECK_AVABILITY(NEW.nama));
END;