How to determine which fields changed in an UPDATE trigger?
You need to compare the old and new values.
IF (old.column1 <> new.column1) THEN
...value of column1 has changed;
ELSE
...value of column1 has not changed;
If your column is nullable, then you also need to check for nulls as the above example will always report that value has not changed when either of the fields is NULL:
IF
( old.column1 is null and new.column1 is not null
or old.column1 is not null and new.column1 is null
or old.column1 <> new.column1
) THEN
...value of column1 has changed;
ELSE
...value of column1 has not changed;
Firebird 2.0 introduces easier way to compare with NULLs involved. You can use IS DISTINCT FROM comparison:
IF (old.column1 IS DISTINCT FROM new.column1) THEN
...value of column1 has changed;
ELSE
...value of column1 has not changed;
If you need to check all columns, it might be hard to write all the checks manually, but you can use a simple query that reads the system tables and generates the statements. Here's an example for the EMPLOYEE table (works with all versions of Firebird):
select 'if (new.' || rdb$field_name || ' is null and old.' ||
rdb$field_name || ' is not null or new.' || rdb$field_name ||
'is not null and old.' || rdb$field_name || ' is null or new.' ||
rdb$field_name || ' <> old.' || rdb$field_name || ') then'
from rdb$relation_fields
where rdb$relation_name = 'EMPLOYEE';