Trigger only if Table if updated

Question

I want to trigger the table after update only if any change is made in the table.

I tried this

but it is giving error. My code is

CREATE TRIGGER Trans_SubCategory_update AFTER UPDATE ON Trans_SubCategory FOR EACH ROW BEGIN IF NEW.ts <> OLD.ts THEN
INSERT INTO Sync_activities (table_name,table_id,admin_id,action) VALUES('Trans_SubCategory',New.id,New.admin_id,'update'); END IF; END;

It is giving 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 '' at line 6 .

But if I use

CREATE TRIGGER Trans_SubCategory_update AFTER UPDATE ON Trans_SubCategory FOR EACH ROW INSERT INTO Sync_activities (table_name,table_id,admin_id,action) VALUES('Trans_SubCategory',New.id,New.admin_id,'update');

I add the trigger but triggers even if no change is made in table.

Answer 1

The thing is - you're forgetting to set proper delimiter. Your first syntax contains multiple operators inside trigger - and they need to be delimited by ; - but you're not changing global delimiter - thus, MySQL treats that as syntax end - and, therefore, that's an error (because trigger isn't completed, obviously).

Your second syntax is executed completely since it has only one operator (and that stands for trigger end as well) - you've not enclosed it by BEGIN..END

To fix the issue, just do:

DELIMITER //
CREATE TRIGGER Trans_SubCategory_update AFTER UPDATE ON Trans_SubCategory 
FOR EACH ROW 
BEGIN 
  IF NEW.ts != OLD.ts THEN
    INSERT INTO Sync_activities (table_name,table_id,admin_id,action) VALUES ('Trans_SubCategory', NEW.id, NEW.admin_id,'update');
  END IF; 
END;//
DELIMITER ;