CODEWITHSUNDEEP
mysqlsqltriggers
Hitesh Kumar
Hitesh Kumar

Reputation: 653

Trigger to update table based on another table update in mysql

I have two tables
1. Tag
2. Triger_testing

Tag desc
id int, is_active (tinyint)

Trigger_Testing Desc
tag_id (int), is_active(tinyint)

I want to create a trigger on tag table update which update trigger_testing table. So if tag.is_active is set to 0 the trigger must fire and update trigger_testing table and set trigger_testing.is_active=0 where trigger_testing.tag_id=tag.id.

I tried to create a trigger in MYSQL but getting syntax exception. Can someone help me out in resolving that issue.

Here is the code : - 

CREATE TRIGGER update_trigger_testing AFTER UPDATE ON tag 
FOR EACH ROW
 BEGIN
    IF NEW.is_active=0 THEN
       UPDATE trigger_testing SET is_Active=0 WHERE tag_id=NEW.id   
    END IF
END$$

DELIMITER;

The error I am getting is :

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF END$$ DELIMITER' at line 6

Upvotes: 0

Views: 204

Answers (1)

Mikhail Timofeev
Mikhail Timofeev

Reputation: 2169

CREATE TRIGGER update_trigger_testing AFTER UPDATE ON tag 
FOR EACH ROW
 BEGIN
    IF NEW.is_active=0 THEN
       UPDATE trigger_testing SET is_Active=0 WHERE tag_id=NEW.id;   
    END IF;
END;

Upvotes: 1

Related Questions