CODEWITHSUNDEEP
mysqltriggers
Andreas Jarbol
Andreas Jarbol

Reputation: 745

MySQL trigger conditional logic has unexpected behavior

I'm having issues using MySQL triggers to keep item counts as users add or remove them. We have two basic tables with following columns:

user_data

item_data

The tables above have more columns, but they're not relevant to the problem. Upon UPDATE and INSERT events user_data.item_count should be updated accordingly. I therefore have the following trigger setup

The insert trigger looks like this:

CREATE TRIGGER insertItem 
AFTER INSERT ON `item_data`
FOR EACH ROW
BEGIN
    IF( NEW.`type` = 'inStock') THEN
        UPDATE `user_data`
        SET `item_count` = `item_count` + 1
        WHERE `user_id` = NEW.`user_id`;
    END IF;
END;

The update trigger looks like this:

CREATE TRIGGER updateItem 
AFTER UPDATE ON `item_data`
FOR EACH ROW
BEGIN
    IF (NEW.`type` = 'inStock') THEN
        IF OLD.`active` = 1 AND NEW.`active` = 0 THEN
            SET @change = -1;
        ELSEIF OLD.`active` = 0 AND NEW.`active` = 1 THEN
            SET @change = 1;
        END IF;
        IF @change IS NOT NULL THEN
            UPDATE `user_data`
            SET `item_count` = `item_count` + @change
            WHERE `user_id` = NEW.`user_id`;
        END IF;
    END IF;
END;

The problem arises when I run a maintenance script that updates parameters seemingly unrelated to the triggers. I get the error:

SQLSTATE[22003]: Numeric value out of range: 
1690 BIGINT UNSIGNED value is out of range in
'(`user_data`.`item_count` + (@`change`))' 
(SQL: update `item_data` set `version_id` = 397928 where `item_id` = 159186)

This clearly arises when a negative change occurs on an item where user_data.item_count = 0, but how can simply updating the version_id trigger this change given the definition of my triggers, and why is the change negative?

Upvotes: 0

Views: 44

Answers (1)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

@change preserves the value in active session, you must reset that value at begin of trigger:

...
BEGIN
    SET @change = null;

...

Upvotes: 1

Related Questions