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

Gowri Naidu R · Accepted Answer · 2014-02-13 09:09:23Z

2

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 ;

edited Feb 13, 2014 at 9:09

answered Feb 13, 2014 at 7:56

Gowri Naidu R

2,0342 gold badges14 silver badges15 bronze badges

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

3 Comments

Gowri Naidu R Over a year ago

You exucute this query now, I have executed myself its working fine. here I thought you want to update last_updated_date is current time either before or after insert table1 particular record so I have modified like that .

Gowri Naidu R Over a year ago

Execute the above query ,just you change table name and field name . It will work. I have executede the above again ,its working fine. Still you are facing issue just send me the error

Ghost Answer Over a year ago

it gives the error for 'new' keyword for field name.

Vikram Jain · Accepted Answer · 2014-02-13 07:40:12Z

0

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;//

answered Feb 13, 2014 at 7:40

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

trigger working issue in mysql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related