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;
MySQL does not provide a way of enabling or disabling triggers natively. Dropping the trigger and recreating it later on is one solution, however, this can be complex and someone could forget to re-create a trigger after an operation has been performed.
Triggers can be re-enabled by using ENABLE TRIGGER. DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE. Changing the trigger by using the ALTER TRIGGER statement enables the trigger.
The ALTER TRIGGER statement is used to disable a trigger.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With