trigger working issue in mysql

Question

I have an issue with my mysql database trigger. I have a table in my database which has two field 'created date' and 'last updated date'. I am using 'TimeStamp' datatype for 'created date' and 'date' datatype for 'last updated date'.

I made a trigger which is used for updating date in 'last updated date' field. My trigger code is here

    CREATE TRIGGER `trigger_name` AFTER Insert ON `table1`
      FOR EACH ROW
    BEGIN
         update table1 set last_updated_date = NOW()
          where id = (select top id from table1)
END;

I have no idea where I am wrong. please suggest me to resolve this problem.

Please frenkly ask if any issue to understand.

thanks

Answer 1

you have an invalid sql syntax in sub query. use LIMIT instead TOP

The TOP clause works on MSSQL server.

Execute the below query it will work.

DELIMITER $$
CREATE TRIGGER `trigger_name` BEFORE INSERT ON `table1`
FOR EACH ROW
BEGIN
  SET NEW.last_updated_date = NOW(); 
END;$$
DELIMITER ;

Answer 2

CREATE TRIGGER `trigger_name`
AFTER INSERT ON `table1`
FOR EACH ROW
BEGIN

update table1 set last_updated_date = NOW()
      where id = (select top id from table1)


END;//