Bug #7996 @variable set in trigger but old value used in UPDATE
Submitted: 18 Jan 2005 23:38 Modified: 11 Apr 2005 5:46
Reporter: John David Duncan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Any (All)
Assigned to: Dmitry Lenev CPU Architecture:Any

[18 Jan 2005 23:38] John David Duncan
Description:
Even if a BEFORE UPDATE trigger sets the value of an @variable,
the variable's old value is used during the update.

How to repeat:
create table foo (a int, b int, c int );
insert into foo values (1,2,3);
create trigger t1 before update on foo for each row set @a = a + b;

set @a := 30;

update foo set a = @a, b = @a + c;

mysql> select * from foo;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   30 |   33 |    3 |
+------+------+------+

[ Expected result: 3,6,3 ]
[18 Jan 2005 23:42] John David Duncan
Oh, the following trigger also does not do the job:

create trigger t1 before update on foo for each row set @a = old.a + old.b;
[11 Apr 2005 5:46] Dmitry Lenev
Hi, JD!

This is not a bug. You observe expected behavior which is consistent with behavior specified by SQL standard.

Things should happen in the following order according to the standard:
1) New values for columns which are specified by SET clause are calculated. These values are assigned to the columns of NEW transition variable (representing new version of row being updated). Old column values are assigned to the columns of OLD transition variable (which represents old version of row being updated).
2) Trigger body is executed. It can change value of NEW transition variable using assignment operator (e.g. "set new.column:=10").
3) Old value of row being updated is replaced with value of NEW transition variable.

As you see change of value of user variable which is done in trigger body should not affect new value of column. Instead you can change these values by direct assignment
to the columns of NEW transition variable.

Anyway thanks for reporting this. It is exposed at least one other bug!