CODEWITHSUNDEEP
sqltriggersmariadbcascading-deletes
London Smith
London Smith

Reputation: 1659

Trigger ON BEFORE error

I have two tables and would like to create a trigger to delete on ticket_reply when a ticket is deleted:

ticket:

+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| id          | int(8) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id     | int(8) unsigned | NO   | MUL | NULL    |                |
| status      | varchar(6)      | NO   |     | opened  |                |
| subject     | varchar(100)    | NO   | MUL | NULL    |                |
| message     | text            | NO   |     | NULL    |                |
+-------------+-----------------+------+-----+---------+----------------+

And ticket_reply:

+-----------+-----------------+------+-----+---------+----------------+
| Field     | Type            | Null | Key | Default | Extra          |
+-----------+-----------------+------+-----+---------+----------------+
| id        | int(8) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id   | int(8) unsigned | NO   |     | NULL    |                |
| ticket_id | int(8) unsigned | NO   | MUL | NULL    |                |
| message   | text            | NO   |     | NULL    |                |
+-----------+-----------------+------+-----+---------+----------------+

My trigger:

CREATE TRIGGER `ticket_delete` BEFORE DELETE ON ticket
FOR EACH ROW
BEGIN
DELETE FROM ticket_reply
    WHERE ticket_reply.ticket_id = ticket.id;
END

I get 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 '' at line 5

Why?

Thanks.

Upvotes: 0

Views: 36

Answers (1)

elenst
elenst

Reputation: 3987

Either use delimiters, or skip BEGIN / END :

DELIMITER $
CREATE TRIGGER `ticket_delete` BEFORE DELETE ON ticket
FOR EACH ROW
BEGIN
DELETE FROM ticket_reply
    WHERE ticket_reply.ticket_id = ticket.id;
END $
DELIMITER ;

or

CREATE TRIGGER `ticket_delete` BEFORE DELETE ON ticket
FOR EACH ROW
DELETE FROM ticket_reply
    WHERE ticket_reply.ticket_id = ticket.id;

It will make the syntax error go away; but your trigger is wrong, in fact it should be

CREATE TRIGGER `ticket_delete` BEFORE DELETE ON ticket
FOR EACH ROW
DELETE FROM ticket_reply
    WHERE ticket_reply.ticket_id = OLD.id;

(notice OLD.id instead of ticket.id).

Upvotes: 1

Related Questions