execute statement

[Sergio]

Hello! I'm trying to do a trigger to maintain a history table. I'm using (for the first time!) execute statement. What I want to do is very simple: if a field change I save the old value in the history

When I execute the trigger I get an error:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, column 1.
if.

I'm sure I'm using "execute statement" in the wrong way !!!

this is the trigger:


CREATE OR ALTER trigger tlm_maestro_au0 for tlm_maestro
active after update position 0
AS
declare variable loc_nuevo_id id;
declare variable loc_ejecutar descripcion_larga;
begin

loc_nuevo_id = gen_id(gen_tlm_maestro_hist,1);

insert into tlm_maestro_hist (id,modificado) values (:loc_nuevo_id, current_timestamp);

for
select
'if (new.' || trim(rdb$field_name) || ' is distinct from old.' || trim(rdb$field_name) ||
') then update tlm_maestro_hist set ' || trim(rdb$field_name) || ' = old.' || trim(rdb$field_name) || ' where id = :loc_nuevo_id;'
from
rdb$relation_fields
where
rdb$relation_name = 'TLM_MAESTRO'
into
:loc_ejecutar
do
begin
execute statement loc_ejecutar;
end

end

[Mark Rotteveel]

Post by Sergio
Hello! I'm trying to do a trigger to maintain a history table. I'm using (for the first time!) execute statement. What I want to do is very simple: if a field change I save the old value in the history
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, column 1.
if.
I'm sure I'm using "execute statement" in the wrong way !!!

EXECUTE STATEMENT is for executing queries only, your code is not just a
query as it contains an IF statement. I think for your purposes you need
to use EXECUTE BLOCK
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-execblock.html

Mark

[Sergio]

Post by Mark Rotteveel
EXECUTE STATEMENT is for executing queries only, your code is not just a
query as it contains an IF statement. I think for your purposes you need
to use EXECUTE BLOCK
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-execblock.html

Thanks Mark!

Yes... I've been reading it before, but I didn't found any example similar of what I want to do...

Perhaps is no possible at all?

Anyway, I was "inspired" in a firebird FAQ

http://www.firebirdfaq.org/faq133/

[Mark Rotteveel]

Post by Sergio

Post by Mark Rotteveel
EXECUTE STATEMENT is for executing queries only, your code is not just a
query as it contains an IF statement. I think for your purposes you need
to use EXECUTE BLOCK
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-execblock.html

Thanks Mark!
Yes... I've been reading it before, but I didn't found any example similar of what I want to do...
Perhaps is no possible at all?

I think you need to create an EXECUTE BLOCK statement as text and then
execute that using EXECUTE STATEMENT, however I am not actually sure if
that would give you access to the NEW and OLD context tables.

Post by Sergio
Anyway, I was "inspired" in a firebird FAQ
http://www.firebirdfaq.org/faq133/

That is more an example of how you can use a query to write the trigger
code, but the result of that query will still be the normal code inside
the trigger.

Mark

[Alexandre Benson Smith]

Post by Mark Rotteveel
I think you need to create an EXECUTE BLOCK statement as text and then
execute that using EXECUTE STATEMENT, however I am not actually sure if
that would give you access to the NEW and OLD context tables.
Mark

There is no way to access NEW and OLD context variables inside EXECUTE
STATEMENT.

To achieve what he wants he will need to write an app (or stored
procedure, or whatever) that loops trough the fields and generate the
trigger PSQL code like


CREATE OR ALTER trigger tlm_maestro_au0 for tlm_maestro
active after update position 0 AS
declare variable loc_nuevo_id id;
declare variable loc_ejecutar descripcion_larga;
begin

loc_nuevo_id = gen_id(gen_tlm_maestro_hist,1);

insert into tlm_maestro_hist (id,modificado) values (:loc_nuevo_id, current_timestamp);


IF (new.Field1 is distinct from old.Field1) then
update tlm_maestro_hist set Field1 = old.Field1 where ID =
:Loc_Nuevo_ID;

IF (new.Field2 is distinct from old.Field2) then
update tlm_maestro_hist set Field2 = old.Field2 where ID = :Loc_Nuevo_ID;

IF (new.Field3 is distinct from old.Field3) then
update tlm_maestro_hist set Field3 = old.Field3 where ID = :Loc_Nuevo_ID;

end

[Milan Babuskov]

Post by Alexandre Benson Smith
To achieve what he wants he will need to write an app (or stored
procedure, or whatever) that loops trough the fields and generate the
trigger PSQL code like

No need to write anything. In FlameRobin, you can right-click the table,
select "Generate code" and then "Create change trigger for table".

It will display a dialog to select the desired columns and it will
generate to complete trigger code.

HTH

[Alexandre Benson Smith]

Post by Sergio
Hello! I'm trying to do a trigger to maintain a history table. I'm using (for the first time!) execute statement. What I want to do is very simple: if a field change I save the old value in the history
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, column 1.
if.
I'm sure I'm using "execute statement" in the wrong way !!!
CREATE OR ALTER trigger tlm_maestro_au0 for tlm_maestro
active after update position 0
AS
declare variable loc_nuevo_id id;
declare variable loc_ejecutar descripcion_larga;
begin
loc_nuevo_id = gen_id(gen_tlm_maestro_hist,1);
insert into tlm_maestro_hist (id,modificado) values (:loc_nuevo_id, current_timestamp);
for
select
'if (new.' || trim(rdb$field_name) || ' is distinct from old.' || trim(rdb$field_name) ||
') then update tlm_maestro_hist set ' || trim(rdb$field_name) || ' = old.' || trim(rdb$field_name) || ' where id = :loc_nuevo_id;'
from
rdb$relation_fields
where
rdb$relation_name = 'TLM_MAESTRO'
into
:loc_ejecutar
do
begin
execute statement loc_ejecutar;
end
end

There is no IF fucntion in FB, did you meant IIF ???

see you !

[Mark Rotteveel]

Post by Alexandre Benson Smith
There is no IF fucntion in FB, did you meant IIF ???

IF does exist in PSQL.

Mark

[Alexandre Benson Smith]

Post by Mark Rotteveel

Post by Alexandre Benson Smith
There is no IF fucntion in FB, did you meant IIF ???

IF does exist in PSQL.
Mark

Yes, I know...

But at first glance I thought he was trying to use IIF since it's inside
a SELECT statement.

After you mentioned EXECUTE BLOCK I re-read the post and saw what he
really wants to do...