Re: [firebird-support] Update only changed tuple/ execute if-statement via execute statement?
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 aharri...@ibphoenix.com [firebird-support] firebird-support@yahoogroups.com: 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
[firebird-support] Update only changed tuple/ execute if-statement via execute statement?
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. 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
RE: [firebird-support] Update only changed tuple/ execute if-statement via execute statement?
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
Re: [firebird-support] Update only changed tuple/ execute if-statement via execute statement?
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
