I have 2 tables: comments
and comments_likes
.
comments
id
message
likes
triggers:
AFTER DELETE
DELETE FROM comments_likes WHERE comment_id = OLD.id;
comments_likes
id
comment_id
triggers:
AFTER INSERT
UPDATE comments SET likes = likes + 1 WHERE comments.id = NEW.comment_id;
AFTER DELETE
UPDATE comments SET likes = likes - 1 WHERE comments.id = OLD.comment_id;
AFTER UPDATE
**omited code, updates comments**
So the question is, can I disable the triggers when activating them from another trigger?
What I want is do something likes this:
AFTER DELETE
IF NOT called_from_another_trigger() THEN
UPDATE comments SET likes = likes - 1 WHERE comments.id = OLD.comment_id;
END IF;
[EDIT]
A non optimized solution would be (very slow query... makes a query for each LIKE register):
BEGIN
IF (SELECT id FROM comments WHERE comments.id = OLD.comment_id) THEN
UPDATE comments SET comments.cache_likes = comments.cache_likes - 1 WHERE comments.id = OLD.comment_id;
END IF;
END
UPDATE LOW PRIORITY
and IGNORE
don't works.
[EDIT 2]
I have another idea, is possible to set a global variable in the first trigger and read it from the other trigger?
Ex:
first trigger:
@disable_triggers = true;
// do the stuff that calls another triggers
@disable_triggers = false;
other trigger:
if @disable_triggers = false then
// do the stuff
end if;
To disable triggers you can do:
Trigger 1
SET @disable_trigger = 1;
// do stuff that calls trigger 2
SET @disable_trigger = NULL;
Trigger 2
IF @disable_trigger IS NULL THEN
// do stuff only if called from a query and not from trigger 1
END IF;