IF in MySQL trigger [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 9 years ago.

Trying to create MySql trigger

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 error:

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 'END IF' at line 6

MySQL version: 5.1.41-community What am I doing wrong?

UPD1. This doesn't 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 t开发者_StackOverflowhe manual that corresponds to your MySQL server version for the right syntax to use near 'END IF END' at line 6

I have root access and using MySql Workbench 5.2.31 CE

---

This works in my machine!

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>

---

it looks like a missing semicolon

PID=NEW.PID;

---

In phpMyAdmin, you can create the trigger in the SQL window.

You may have to set the delimieter to something like $$ instead of the default ;. You can change this easily from the bottom of the SQL window.

In addition, make sure you close your trigger block with the END command-

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 ;

---

U have to some syntex problem ..use like this

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//