CODEWITHSUNDEEP
mysqltriggerssql-update
Gauthier
Gauthier

Reputation: 1266

Trigger on update

I would like to define a trigger to set a column ( deleted_at ) at ' NOW() ' when a row is modified. I tried things like this =>

CREATE TRIGGER test AFTER INSERT ON articles
FOR EACH
ROW
BEGIN
    UPDATE articles SET updated_at = NOW() ;
END;

But i have errors like this =>

#1064 - Syntax error near '' line 5

Upvotes: 0

Views: 130

Answers (3)

Gianluca Colombo
Gianluca Colombo

Reputation: 829

You have to change the line

 UPDATE articles SET updated_at = NOW() ;

with this one

 SET NEW.updated_at = NOW() ;

Upvotes: 0

carlosherrera
carlosherrera

Reputation: 187

The thing is you're updating your table in the trigger itself.

Why don't you use a before insert trigger like this?

CREATE TRIGGER test BEFORE INSERT ON articles
FOR EACH
ROW
BEGIN
    set NEW.updated_at = NOW() ;
END;

That way you are sure your field is updated in every insert.

Upvotes: 1

fancyPants
fancyPants

Reputation: 51928

Either you remove BEGIN and END (if you have only one statement in your trigger) or you put a different delimiter. Also you don't do an update in an update trigger. Make it a BEFORE trigger and set the value directly.

DELIMITER $$
CREATE TRIGGER test BEFORE INSERT ON articles
FOR EACH
ROW
BEGIN
    SET NEW.updated_at = NOW() ;
END $$
DELIMITER ;

Upvotes: 2

Related Questions