mysql - Creating Last Modified SQL Trigger -

mysql - Creating Last Modified SQL Trigger -

i trying create trigger update last_modified field when row updated.

this have tried:

use `mydb`; delimiter $$ drop trigger if exists mydb.products_aupd$$ utilize `mydb`$$ create trigger `products_aupd` after update on products each row begin set new.`last_modified` = now(); end; $$ delimiter ;

the problem mysql workbench wont allow me save (i'm assuming there wrong can't find out what)

turns out version of mysql workbench using(6.0xxx) had bug in played havoc triggers. updated 6.2xxx.

that first problem.

the sec problem was using new keyword in after trigger not allowed:

within trigger body, can refer columns in subject table (the table associated trigger) using aliases old , new. old.col_name refers column of existing row before updated or deleted. new.col_name refers column of new row inserted or existing row after updated.

so new trigger looks this:

create definer = current_user trigger `mydb`.`products_before_update` before update on `products` each row begin set new.`last_modified` = now(); end;

notice there no use or delimiter statement? because new version of mysql workbench puts in (including end delimiter $$):

use `mydb`; delimiter $$ utilize `mydb`$$ drop trigger if exists `mydb`.`products_before_insert` $$ utilize `mydb`$$ create definer = current_user trigger `mydb`.`products_before_insert` before insert on `products` each row begin set new.`last_modified` = now(); end;$$

mysql sql database-design syntax