CODEWITHSUNDEEP
mysqlsql
Ananth Sathvick
Ananth Sathvick

Reputation: 168

MySQL trigger end if syntax error

Unable to create trigger on update of specific field

delimiter //
CREATE TRIGGER `add_event` AFTER  UPDATE ON `order_table` 
  FOR EACH ROW 
  IF NEW.status <=> OLD.status THEN
    INSERT INTO `events` SET 
      events.status = NEW.status, 
      events.order_id = NEW.order_id, 
      events.time_stamp = CURRENT_TIMESTAMP
  END IF;
delimiter ;

I am getting this 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 'END IF;

Upvotes: 2

Views: 2431

Answers (3)

munsifali
munsifali

Reputation: 1732

There is no syntax error in below mysql trigger.

delimiter //
 CREATE TRIGGER add_event AFTER UPDATE ON order_table
    FOR EACH ROW
    BEGIN
         IF NEW.status <=> OLD.status THEN
            INSERT INTO `events` 
            SET events.status = NEW.status, 
            events.order_id = NEW.order_id, 
            events.time_stamp = CURRENT_TIMESTAMP;
         END IF;
     END;//
 delimiter ;

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17640

drop table if exists t;
drop table if exists events;

create table t(order_id int, status varchar(1));
create table events(order_id int, time_stamp timestamp,status varchar(1));
drop trigger if exists t;
delimiter //
CREATE TRIGGER t after update ON `t` 
 FOR EACH ROW 
 begin
 IF NEW.status <> OLD.status THEN
    INSERT INTO `events` 
        SET events.status = NEW.status, 
                events.order_id = NEW.order_id, 
                 events.time_stamp = CURRENT_TIMESTAMP;
END IF ;
end //
 delimiter ;

 insert into t values(1,1);
 insert into events values(1,now(),1);
 update t set status = 2 where order_id = 1;
 select * from t;
 select * from events;

MariaDB [sandbox]>  select * from t;
+----------+--------+
| order_id | status |
+----------+--------+
|        1 | 2      |
+----------+--------+
1 row in set (0.00 sec)

MariaDB [sandbox]>  select * from events;
+----------+---------------------+--------+
| order_id | time_stamp          | status |
+----------+---------------------+--------+
|        1 | 2018-08-06 10:16:35 | 1      |
|        1 | 2018-08-06 10:16:35 | 2      |
+----------+---------------------+--------+
2 rows in set (0.00 sec)

Upvotes: 1

Deepak Kumar
Deepak Kumar

Reputation: 668

Why are you using if statement. You are checking everything <=>. Here is the solution : 

CREATE TRIGGER `add_event` AFTER  UPDATE ON `order_table` 
 FOR EACH ROW 
 BEGIN
 INSERT INTO `events` SET events.status = NEW.status, events.order_id = NEW.order_id, events.time_stamp = CURRENT_TIMESTAMP;
END;
 delimiter ;

Upvotes: 0

Related Questions