Chakreshwar Sharma Chakreshwar Sharma - 6 months ago 33
SQL Question

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

Answer

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 ;