MySQL trigger conditional logic has unexpected behavior -

MySQL trigger conditional logic has unexpected behavior -

i'm having issues using mysql triggers maintain item counts users add together or remove them. have 2 basic tables next columns: user_data

user_id - unsigned int(11) primary key ai item_count - unsigned int(11)

item_data

item_id - unsigned int(11) primary key ai user_id - unsigned int(11) fk(user_data.user_id) version_id - unsigned int(11) type - varchar(32) active - tinyint

the tables above have more columns, they're not relevant problem. upon update , insert events user_data.item_count should updated accordingly. hence have next trigger setup the insert trigger looks this:

create trigger insertitem after insert on `item_data` each row begin if( new.`type` = 'instock') update `user_data` set `item_count` = `item_count` + 1 `user_id` = new.`user_id`; end if; end;

the update trigger looks this:

create trigger updateitem after update on `item_data` each row begin if (new.`type` = 'instock') if old.`active` = 1 , new.`active` = 0 set @change = -1; elseif old.`active` = 0 , new.`active` = 1 set @change = 1; end if; if @change not null update `user_data` set `item_count` = `item_count` + @change `user_id` = new.`user_id`; end if; end if; end;

the problem arises when run maintenance script updates parameters seemingly unrelated triggers. error:

sqlstate[22003]: numeric value out of range: 1690 bigint unsigned value out of range in '(`user_data`.`item_count` + (@`change`))' (sql: update `item_data` set `version_id` = 397928 `item_id` = 159186)

this arises when negative alter occurs on item user_data.item_count = 0, how can updating version_id trigger alter given definition of triggers, , why alter negative?

@change preserves value in active session, must reset value @ begin of trigger:

... begin set @change = null; ...

mysql triggers