This question is somwhere LO-base and Firebird related.
What I want to do is write an after update trigger to update another field in the same table (copying part of the updated field into the second one).
The trigger seems at first fairly straightforward, but of course updating by a trigger in the same table triggers the update, triggers …
I found a solution - so I thought - in [sql - firebird - after insert or update trigger - Stack Overflow] but when i try to get that into my trigger, I get above error when creating the trigger.
Chapter 8.1 of the Firebird manual says on this subject “Available inDSQL, PSQL * As a declared UDF it should be available in ESQL”. But I have no idea where LO Base fits is somewhere.
The trigger would read
create trigger pandcomupd for "Panden"
after update as
BEGIN
IF RDB$GET_CONTEXT('USER_TRANSACTION', 'MY_LOCK') IS NULL THEN
BEGIN
RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', 1);
if (old."Pandnrext" is null or new."Pandnrext" <> old."Pandnrext")
then
update "Panden" set "Pandcombi" = "Pandnr" || coalesce("Pandnrext", ' ');
RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', NULL);
end
WHEN ANY DO
BEGIN
RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', NULL);
EXCEPTION;
END
END