MySQL trigger end if syntax error

Question

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;

No such thing as and AFTER UPDATE, INSERT trigger - you need one of each. — P.Salmon
– P.Salmon, Commented Aug 6, 2018 at 9:05
Now I just think you need a ; after CURRENT_TIMESTAMP to terminate your statement properly. — ADyson
– ADyson, Commented Aug 6, 2018 at 9:12
after FOR EACH ROW you need a begin key word and after end if;you Need an end;// — hotfix
– hotfix, Commented Aug 6, 2018 at 9:13
SET statement has no effect in an AFTER trigger because the row change will have already occurred — munsifali
– munsifali, Commented Aug 6, 2018 at 9:15

munsifali · Accepted Answer · 2018-08-06 09:21:20Z

1

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 ;

answered Aug 6, 2018 at 9:21

munsifali

1,7432 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ananth Sathvick Over a year ago

Works!! Thankyou :)

ADyson Over a year ago

@deadman Ananth can't upvote, doesn't have enough rep points. I did though.

P.Salmon · Accepted Answer · 2018-08-06 09:18:56Z

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)

Deepak Kumar · Accepted Answer · 2018-08-06 09:13:02Z

0

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 ;

answered Aug 6, 2018 at 9:13

Deepak Kumar

6666 silver badges14 bronze badges

3 Comments

Ananth Sathvick Over a year ago

Even I need to check if paricular field is updated, Insert if updated

Deepak Kumar Over a year ago

which version you are using?

Deepak Kumar Over a year ago

You are checking everything. This IF statement always return true.

Collectives™ on Stack Overflow

MySQL trigger end if syntax error

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related