CODEWITHSUNDEEP
mysqlsyntaxtriggersmariadb
undefinedman
undefinedman

Reputation: 670

SQL syntax error in trigger

Can someone tell me what this sql does not work? Where is the syntax error?

SET NEW.`modified` = CURTIME() WHERE `id` = NEW.id;

Error:

One or more errors have occured while processing your request: The following query has failed: 

CREATE DEFINER=`root`@`localhost` TRIGGER `event_name` 
BEFORE UPDATE ON `clients` 
FOR EACH ROW 
SET NEW.`modified` = CURTIME() WHERE `id` = NEW.id;

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE id = NEW.id' at line 1

Image shows the error http://s23.postimg.org/rhsf2x1tn/screenshot.png

Upvotes: 0

Views: 1362

Answers (1)

Menelaos
Menelaos

Reputation: 26004

You don't need the where condition to specify the row being changed.

For every row being updated, you set the specific column value. See the example below as well as the quoted reference.

Quoting/Referencing from here:

DELIMITER |
CREATE TRIGGER event_name BEFORE UPDATE ON clients 
FOR EACH ROW 
BEGIN
    SET NEW.date_modify = NOW();
END; 
|
DELIMITER ;

Upvotes: 3

Related Questions