CODEWITHSUNDEEP
mysql
Chakreshwar Sharma
Chakreshwar Sharma

Reputation: 2610

Syntax Error in Mysql Trigger with if condition

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

Upvotes: 1

Views: 124

Answers (2)

Sandeep
Sandeep

Reputation: 1532

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 ;

Upvotes: 2

Conrad Lotz
Conrad Lotz

Reputation: 8818

Just add the semi-colon after the END IF 

END IF;

Upvotes: 0

Related Questions