Re: Update Triggers...how to figure out which columns have changed

[Roland Bouman]

I cannot reproduce your findings. It works perfectly for me. I can also found no evidence for the NULL. Here is my test:

delimiter //

use test

create table test_updating(
name varchar(64)
, num int unsigned
)
//

create table updating_log(
id int auto_increment primary key
, message varchar(255)
)
//

create trigger tgr_updating
before update on
test_updating
for each row
begin
if new.name != old.name
or new.name is null and old.name is not null
or old.name is null and new.name is not null then
insert
into updating_log(message)
values (
concat(
'name: '
, coalesce(old.name,'NULL')
, ' -> '
, coalesce(new.name,'NULL')
)
);
end if;
end;
//

insert into test_updating(name,num) values ('house',1)
//

here are the actual tests:

mysql> update test_updating set name = 'house' where num = 1
-> //
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> select * from updating_log
-> //
Empty set (0.00 sec)

(to be expected, no change occurred)

mysql> update test_updating set name = 'house1' where num = 1//
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from updating_log
-> //
+----+-----------------------+
| id | message |
+----+-----------------------+
| 1 | name: house -> house1 |
+----+-----------------------+

(house was changed to house1, and this is detected correctly)

mysql> update test_updating set name = 'house1',num=2 where num = 1//
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from updating_log
-> //
+----+-----------------------+
| id | message |
+----+-----------------------+
| 1 | name: house -> house1 |
+----+-----------------------+
1 row in set (0.00 sec)

(update of num performed, but not of name. nothing detected as expected)

mysql> update test_updating set name = null,num=2 where num = 2//
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from updating_log
-> //
+----+-----------------------+
| id | message |
+----+-----------------------+
| 1 | name: house -> house1 |
| 2 | name: house1 -> NULL |
+----+-----------------------+
2 rows in set (0.00 sec)

(name changed to null, detected as expected)

mysql> update test_updating set name = 'house',num=2 where num = 2//
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from updating_log
-> //
+----+-----------------------+
| id | message |
+----+-----------------------+
| 1 | name: house -> house1 |
| 2 | name: house1 -> NULL |
| 3 | name: NULL -> house |
+----+-----------------------+
3 rows in set (0.00 sec)

(changed back from NULL, is detected as expected)

mysql> update test_updating set num=1 where num = 2//
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from updating_log
-> //
+----+-----------------------+
| id | message |
+----+-----------------------+
| 1 | name: house -> house1 |
| 2 | name: house1 -> NULL |
| 3 | name: NULL -> house |
+----+-----------------------+
3 rows in set (0.00 sec)

(Again an update, this time leaving out the name column. In contrary to your findings, no NULL is seen in the new.name column. If so, it would surely 've been detected, like in one of the previous updates)

I am running 5.0.17-nt on windows here, but it should be easy for you to run these test in your setup.