For updating a field called 'last_modified' I'm trying to update this field automatically using a trigger, in stead of changing all update-statements. The field should only be updated when a field value has changed. Problem here is that you have to maintain the triggers if a table field is added, removed or renamed. The fields are stored in rdb$relation_fields, no problem. But building a query comparing the old and new value dynamically is.
create trigger test for test_table active before update position 0 as
declare variable fn char(31);
begin
for select rdb$field_name from rdb$relation_fields where rdb$relation_name = 'test_table' into :fn do
begin
if ('old.'||:fn <> 'new.'||:fn) then
begin
new.last_modified = current_timestamp;
break;
end
end
end
Problem here is that 'old.'||:fn and 'new.':fn not really are comparing values, but literal strings, so the value of the fields cannot be compared. I've seen over here Firebird - get all modified fields inside a trigger that a trigger is attached to a system table, something I don't want to.
Is this fully-automated way of updating the 'last_modified' field not possible in this way? Or do I have to create a stored procedure that deletes al triggers and then recreates them with new fields, once I perform a update on the database (using this code http://www.firebirdfaq.org/faq133/).
