Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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;
like image 798
Wiliam Avatar asked Aug 26 '10 17:08

Wiliam


People also ask

Can we disable trigger in MySQL?

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.

Can we disable a trigger if yes how?

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.

Which of the following statements will be used to disable the trigger?

The ALTER TRIGGER statement is used to disable a trigger.


1 Answers

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;
like image 190
Wiliam Avatar answered Oct 13 '22 23:10

Wiliam