CODEWITHSUNDEEP
mysqlsqltriggers
Paul Zaslavskij
Paul Zaslavskij

Reputation: 654

How to create a valid SQL trigger with condition?

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 !

Upvotes: 1

Views: 248

Answers (2)

Arun_Raja
Arun_Raja

Reputation: 1

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.

Upvotes: 0

nbk
nbk

Reputation: 49409

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 ;

Upvotes: 1

Related Questions