Can anyone tell me what is wrong with my create trigger syntax?

Question

I am experimenting with triggers for the first time.

When I try to create a trigger using the following:

CREATE TRIGGER t_foldersPrivate BEFORE DELETE ON foldersPrivate
FOR EACH ROW BEGIN
 DELETE FROM programs WHERE folderType = '0' AND folderID = OLD.ID; 
END;

I receive the following error:

`1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3`

(mysql 5.1.37)

If I get rid fo the delete statement the create trigger statement works fine. So I am assuming it must have something to do with that. But for the life of me I'm not sure what...

Answer 1

You should change the delimiters.

Something like this:

DELIMITER $$

CREATE TRIGGER t_foldersPrivate BEFORE DELETE ON foldersPrivate
FOR EACH ROW BEGIN
 DELETE FROM programs WHERE folderType = '0' AND folderID = OLD.ID; 
END$$

DELIMITER ;

Also, you should check the delete query separately. Is it working?