MySQL IF statement in Trigger

Question

I just want to create trigger in my sql but some error happened

this is the code

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

and this is the error message

#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

please help me

Your statements need terminators (;) - and if you trying to create trigger in mysql did you set the delimiter to something other than ; before trying to create the trigger (and reset it after)? — P.Salmon
– P.Salmon, Commented Feb 19, 2017 at 7:49
You are missing the semicolons after each statement within the body of the trigger. Also, the whole body could be replaced by a single multi-table update statement. — Shadow
– Shadow, Commented Feb 19, 2017 at 7:49

jophab · Accepted Answer · 2017-02-19 12:48:48Z

1

Add semicolons after the statements and change default delimiter(;) before the code of create trigger. Otherwise it will give

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

After the create trigger code make ; as default delimiter

   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 ;

edited Feb 19, 2017 at 12:48

answered Feb 19, 2017 at 9:26

jophab

5,54714 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Badarudin Syah Over a year ago

I still get same error message and for each row with semicolon has extra error message that said "Unexpected character near (;)"

Badarudin Syah Over a year ago

im sorry but it still doesnt working, anyway thanks for your help bro.

jophab Over a year ago

What is the error now. I got it working in my phpmyadmin

Collectives™ on Stack Overflow

MySQL IF statement in Trigger

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related