Syntax Error in Mysql Trigger with if condition

Question

I want to update two table using trigger ,so I am running the below query:

CREATE TRIGGER project_document_trig AFTER INSERT ON object_metadata
FOR EACH ROW 
BEGIN
   IF NEW.group like 'group1' THEN
       UPDATE documents as document
           SET document.projects_count = document.projects_count + 1
           WHERE document.id = NEW.value;
       UPDATE projects as project
           SET project.documents_count = project.documents_count + 1
           WHERE project.id = NEW.resource_id ;
   END IF
END

I am getting syntax error

Sandeep · Accepted Answer · 2016-05-11 10:01:56Z

Use this code and check:

DELIMITER $$

CREATE TRIGGER project_document_trig 
AFTER INSERT ON object_metadata 
FOR EACH ROW 
BEGIN
   IF NEW.group like 'group1' THEN
       UPDATE documents as document
           SET document.projects_count = document.projects_count + 1
           WHERE document.id = NEW.value;
       UPDATE projects as project
           SET project.documents_count = project.documents_count + 1
           WHERE project.id = NEW.resource_id ;
   END IF;
END$$
DELIMITER ;

Alternatively You can use:

DELIMITER $$

CREATE TRIGGER project_document_trig 
AFTER INSERT ON object_metadata 
FOR EACH ROW 
BEGIN
   IF (EXISTS(SELECT 1 FROM object_metadata WHERE NEW.group like 'group1'))
   THEN
       UPDATE documents as document
           SET document.projects_count = document.projects_count + 1
           WHERE document.id = NEW.value;
       UPDATE projects as project
           SET project.documents_count = project.documents_count + 1
           WHERE project.id = NEW.resource_id ;
   END IF;
END$$
DELIMITER ;

Conrad Lotz · Accepted Answer · 2016-05-11 09:58:06Z

0

Just add the semi-colon after the END IF

END IF;

answered May 11, 2016 at 9:58

Conrad Lotz

8,9533 gold badges25 silver badges28 bronze badges

Collectives™ on Stack Overflow

Syntax Error in Mysql Trigger with if condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related