Make a query in mysql without invoking a trigger (How to disable a trigger)

Wiliam picture Wiliam · Aug 26, 2010 · Viewed 15.4k times · Source

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;

Answer

Wiliam picture Wiliam · Aug 26, 2010

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;