-4

This is the table i had created

CREATE TABLE dob (name VARCHAR(20),date VARCHAR(100));

This is the data i had loaded in to the table dob

uttej,2017-08-22
venki,1997-07-15
porna,1995-05-11
pp,1997-07-07

enter image description here

This is the query i had written but there is an error

delimiter |

CREATE TRIGGER test after update on dob
for each row
begin
update dob where date = SELECT NOW();
end;

|

enter image description here need some help ???????

l.g.karolos
  • 1,131
  • 1
  • 10
  • 25
uttej
  • 1
  • 2
  • Would you like to tag your database? sql, mysql, sqlite, .... – Yunnosch Aug 22 '17 at 16:25
  • Do not post pictures of text. Prefer to post a [mcve] in the shape of one line of `create ...` and several lines of `insert ... `. – Yunnosch Aug 22 '17 at 16:26
  • You should set your date column to a valid [date and time type](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html) instead of VARCHAR. Read [When to use VARCHAR and DATE/DATETIME](https://stackoverflow.com/a/4759039/5395709) – M3talM0nk3y Aug 22 '17 at 16:44
  • By replacing varchar with datetime i am getting the output as 00:00:0000 in the place of time and the date is displaying correctly.what i wanted is only date has to be displayed. – uttej Aug 22 '17 at 16:54

1 Answers1

0

This will solve your syntax error:

CREATE trigger test after update on dob
begin
  UPDATE dob SET date = NOW();
end;

But I am concerned that this will not have the outcome you desire, because if the field dob refers to date of birth, then you wouldn't want to be changing that every time the row gets updated...

Dave
  • 822
  • 1
  • 7
  • 17
  • 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 'begin UPDATE dob SET date = NOW()' at line 2 This is the error it is showing ... @Dave – uttej Aug 22 '17 at 16:36