On Sat, May 24, 2014 at 8:51 AM, eric wulfhekel eric.wulfhekel@... [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