Thank you very much for your arguments/tips. Pity that there is no more perfomant "general trigger" - approach. But recreating triggers - maybe over stored procedures - is also a possibility for me.
Thank you. Eric 2014-05-30 17:08 GMT+02:00 Ann Harrison [email protected] [firebird-support] <[email protected]>: > > > On Sat, May 24, 2014 at 8:51 AM, eric wulfhekel [email protected] > [firebird-support] <[email protected]> wrote: > >> >> 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 >> >> > I've left your example below. You're on the right track. The actual > problem you're having is that EXECUTE STATEMENT executes SQL statements. > You're generating a statement that can be used only in triggers and stored > procedures. As Set suggested, a better plan would be to use your current > approach, but generate actual CREATE TRIGGER statements rather than the > PSQL. Yes, that means that you will need to recreate triggers when your > tables change, but you may be able to do that - or at least signal that > maintenance is required - with a DDL trigger. > > Alternately you could generate a series of SQL statements - UPDATE ... > WHERE NEW.<field> IS DISTINCT FROM OLD.<field> - and execute them. > However, that would be horribly inefficient - searching the system tables, > generating a query, passing that query to the engine where it has to be > parsed, compiled, optimized, executed, and released - all of that for every > field in every record you change. I'd bet that performance would be > measured not by wall clock, but by calendar. > > Good luck, > > Ann > > > > > Here's the statement you tried and the error... > > > >> 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. >> 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? >> >> Thank you in advance >> >> >> >
