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';



Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.



All contents are copyright © 2007-2024 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous