On Sat, May 24, 2014 at 8:51 AM, eric wulfhekel eric.wulfhe...@gmail.com [firebird-support] <firebird-support@yahoogroups.com> 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 > > > >
