CODEWITHSUNDEEP
mysqlsqltriggers
Nashwan
Nashwan

Reputation: 63

Deleting a record after updating it under some condition using an SQL trigger

I'm struggling with an unknown syntax error given by MySQL server when executing this query

CREATE TRIGGER del_entry AFTER UPDATE on some_table
FOR EACH ROW BEGIN
  IF NEW.col=0 THEN
    DELETE FROM some_table
    WHERE prim_key = NEW.prim_key;
  END IF;
END;

The sql error given is:

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 5,

which means the where clause line...

Any thought?

Upvotes: 0

Views: 36

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Your syntax is ok, but to add the trigger, you'll need to change the delimiter so MySQL knows where the trigger ends. The normal is ;, but since the trigger contains several, it won't add.

DELIMITER //

CREATE TRIGGER del_entry AFTER UPDATE on some_table
FOR EACH ROW BEGIN
  IF NEW.col=0 THEN
    DELETE FROM some_table
    WHERE prim_key = NEW.prim_key;
  END IF;
END;
// 

DELIMITER ;

An SQLfiddle that adds the exact trigger successfully.

Upvotes: 1

Related Questions