IF when starting MySQL

Question

Trying to create MySql launch

CREATE TRIGGER updVisible AFTER UPDATE ON photos
FOR EACH ROW 
BEGIN
    IF NEW.Status = 2 THEN
        UPDATE otherTable SET IsVisible=0 WHERE PID=NEW.PID
    END IF;
END;

But I got an error:

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to "END IF" on line 6

MySQL Version: 5.1.41 Community What am I doing wrong?

UPD1. It does not help

DELIMITER //
CREATE TRIGGER updVisible AFTER UPDATE ON photos
FOR EACH ROW 
BEGIN
    IF NEW.Status = 2 THEN
        UPDATE otherTable SET IsVisible=0 WHERE PID=NEW.PID
    END IF
END//
DELIMETER ;

Error:

Error code: 1064 You have an error in your SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use next to "END IF END" on line 6

I have root access and use MySql Workbench 5.2.31 CE

Answer 1

It works on my car!

mysql> DELIMITER //
mysql> CREATE TRIGGER test1 AFTER UPDATE ON test
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.itemId = '2' THEN
    ->         UPDATE test1 SET col1=0 WHERE col2=NEW.`value`;
    ->     END IF;
    -> END//
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMiTER ;
mysql> desc test;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| itemId | varchar(100) | YES  |     | NULL    |       |
| key    | varchar(100) | YES  |     | NULL    |       |
| value  | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> desc test1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | varchar(100) | YES  |     | NULL    |       |
| col2  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

Answer 2

it looks like a missing semicolon

PID=NEW.PID;

Answer 3

U syntex.

CREATE TRIGGER updVisible AFTER UPDATE ON photos
FOR EACH ROW 
BEGIN
    IF NEW.Status = 2 THEN
        UPDATE otherTable SET IsVisible=0 WHERE PID=NEW.PID
    END IF
END//

Answer 4

phpMyAdmin SQL.

You may need to install delimieter somehow $$instead of the standard one ;. You can easily change this from the bottom of the SQL window.

Also, make sure you close the trigger block with the command END-

DELIMITER $$

CREATE TRIGGER `tutorial`.`before_delete_carts`  
     BEFORE DELETE ON `trigger_carts` FOR EACH ROW  
     BEGIN  
         DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;  
     END $$ 
DELIMITER ;