How to create a valid SQL trigger with condition?-CodePudding

I’m stucked with creating SQL trigger, that updates publishingDate when Published field of current row became true. I’m tried multiple variants of declaration, but every time got a syntax error. Googling doesn’t gave the key for current case. Hope, you can help. My code is below. Validator I used: https://ru.rakko.tools/tools/36/

delimiter !

CREATE TRIGGER `vc`.`Articles_Updated_trigger`
BEFORE UPDATE
ON `Articles` FOR EACH ROW
BEGIN
  IF new.Published = TRUE 
    SET new.PublishingdDate = CURRENT_TIMESTAMP
  END IF;
END !

CodePudding user response：

If you want to get all rows where the status changed, you need to compare OLD and NEW for that to detect the change

delimiter $$

CREATE TRIGGER `vc`.`Articles_Updated_trigger`
BEFORE UPDATE
ON `Articles` FOR EACH ROW
BEGIN
  IF OLD.Published = FALSE AND NEW.Published = TRUE THEN
    SET new.PublishingdDate = CURRENT_TIMESTAMP;
  END IF;
END$$
DELIMITER ;

CodePudding user response：

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
  
<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name

Create your triggers using this syntax and try your next aspect.