MySQL 触发器中的 IF 语句

Question

我只想在我的 sql 中创建触发器，但发生了一些错误

这是代码

CREATE TRIGGER delete_santri_in_kamar
AFTER UPDATE ON
santri
FOR EACH ROW
BEGIN
DECLARE stat INT
SET stat = select status FROM santri WHERE id_santri=new.id_santri

IF (stat = 0) THEN
DELETE FROM santri_kamar_asrama WHERE id_santri=new.id_santri
END IF

END

这是错误消息

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET stat = select status FROM santri WHERE id_santri=new.id_santri

IF (stat =' at line 7

请帮助我

Answer 1

在语句后添加分号并在创建触发器的代码前更改默认分隔符(;)。否则它会给出

SQL Error(1064): You have an error in your SQL Syntax;

创建触发器代码后将 ; 作为默认分隔符

   DELIMITER $$
    CREATE TRIGGER delete_santri_in_kamar
    AFTER UPDATE ON
    santri
    FOR EACH ROW
    BEGIN
    DECLARE stat INT;
    SET stat = (select status FROM santri WHERE id_santri=new.id_santri);

    IF (stat = 0) THEN
    DELETE FROM santri_kamar_asrama WHERE id_santri=new.id_santri;
    END IF;

    END
$$
DELIMITER ;