php - 如何创建删除 phpmyadmin 的触发器

Question

我有两个表 A 和 B。如果我删除表 A 中的条目，我想使用用户 ID 更新表 B 中条目的状态。我正在使用这段代码:

CREATE TRIGGER blockuserundo BEFORE DELETE ON user_blocked
FOR EACH ROW BEGIN
UPDATE members SET ty_status = '1' WHERE user_id = OLD.block_user_id ;
END;

它显示如下错误:

no new row in the trigger delete

请指导我编写上述场景的查询。

Answer 1

您需要将分隔符设置为 ; 以外的其他内容

DELIMITER |
CREATE TRIGGER blockuserundo BEFORE DELETE ON user_blocked
FOR EACH ROW BEGIN
UPDATE members SET ty_status = '1' WHERE user_id = OLD.block_user_id ;
END;
|
DELIMITER ;

下面的代码按预期工作:

DROP TABLE IF EXISTS user_blocked;
CREATE TABLE user_blocked 
(
    block_user_id INT
);
DROP TABLE IF EXISTS members;
CREATE TABLE members 
(
    user_id INT,
    ty_status CHAR(1)
);

INSERT user_blocked
VALUES (1),(2),(3),(4),(5);

INSERT members
VALUES (1, '0'),(2, '0'),(3, '0'),(8, '0'),(9, '1');

DELIMITER |
CREATE TRIGGER blockuserundo BEFORE DELETE ON user_blocked
FOR EACH ROW BEGIN
UPDATE members SET ty_status = '1' WHERE user_id = OLD.block_user_id ;
END;
|
DELIMITER ;

SELECT * FROM members;

DELETE FROM user_blocked WHERE block_user_id IN(1,2);
SELECT * FROM members;