>Hello, >I would be very happy if anybody could help me. I want to create a update >trigger which sets the current date to a field "modified" >if values has changed. I have a lot of tables with a lot of columns so i want >to work with system table for that task. I build the >if-statement within a select and wat to execute this statement > >Like: > >CREATE TRIGGER PLZTEST_MODIFIED FOR PLZTEST >ACTIVE BEFORE UPDATE POSITION 0 >AS >declare variable stmt varchar(1024); >begin >for > 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 new.MODIFIED >= current_timestamp;' > from rdb$relation_fields > where rdb$relation_name = 'PLZTEST' > into :stmt >do >execute statement stmt; >end > >The resulting statement looks good.
Are you sure? I’ve never tried doing what you’re doing, but would (unfortunately) be surprised if new.* and old.* was available in rdb$relations_fields. >The problem here seems, that i could not execute statements starts with if. >the error occur is > >Dynamic SQL Error. >SQL error code = -104. >Token unknown - line 1, column 1. >if. >Or maybe is there another way to realise that? Rather than writing a general trigger for modifying any table, I think I would recommend that you write a stored procedure that takes a table name as an input parameter that then dynamically creates a CREATE OR ALTER TRIGGER statement specifically targeted towards the table in question (hence a stored procedure that creates specific triggers (DDL) as opposed to one generic trigger). By the way, if you’re using Firebird 2.x, it’s simpler to use IS DISTINCT FROM which treats NULL as if it was a value and have one rather than three comparisons for each field. HTH, Set
