CODEWITHSUNDEEP
stored-procedurestriggersfirebirdfirebird2.5
Pajkec
Pajkec

Reputation: 67

Firebird execute procedure and statement (with result from that procedure as part of it) inside trigger

I want to execute a before update trigger, which checks if all the fields in a row are the same and cancel the update. I'm using Firebird 2.5.

My current approach is this:

My code:

SET TERM ^ ;
ALTER TRIGGER BI_MERILA_STRANKE ACTIVE
BEFORE INSERT OR UPDATE POSITION 0
AS 
declare variable besedilo_primerjave varchar(5000);

BEGIN 
    begin

         if (new.ID_MERILA_STRANKE is null OR new.ID_MERILA_STRANKE = 0) then new.ID_MERILA_STRANKE = gen_id(GEN_ID_MERILA_STRANKE,1);
    end

    begin    
        besedilo_primerjave = execute procedure 
STRING_SESTAVLJEN_ENAKOST_TABEL('MERILA_STRANKE');   -- if this is true then you need to save othervise not

         execute statement besedilo_primerjave || ' THEN BEGIN INSERT INTO SYNC_INFO(TABLE_NAME,ID_COLUMN_NAME,ID_VALUE,DATETIME) 
        VALUES (
            ''MERILA_STRANKE'',
            ''ID_MERILA_STRANKE'',
            NEW.ID_MERILA_STRANKE,
            CURRENT_TIMESTAMP
        );

        END ELSE BEGIN

            exception ENAK_RECORD;

        END';
    end
END^
SET TERM ; ^

When I try to execute this, I get the following error:

Token unknown - line 18, column 10
execute

This is this line of code:

execute statement besedilo_primerjave || ' THEN BEGIN INSERT INTO SYNC_INFO(TABLE_NAME,ID_COLUMN_NAME,ID_VALUE,DATETIME)

I am not working a lot with Firebird, so if anyone has any idea what I'm doing wrong, or if anyone knows a better solution to achieve this, please help me.

I need to do it on database, probably better approach would be to do it in software, but I can not do that.

Upvotes: 1

Views: 4094

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109015

You are wrong about the problematic line. The actual problem is on

besedilo_primerjave = execute procedure STRING_SESTAVLJEN_ENAKOST_TABEL('MERILA_STRANKE');

You can't have execute procedure after assignment, you need to use

execute procedure STRING_SESTAVLJEN_ENAKOST_TABEL('MERILA_STRANKE')
    returning_values :besedilo_primerjave;

Your next statement cannot work as execute statement cannot be used to execute snippets of PSQL (procedural SQL) like that, it can only execute normal DSQL (dynamic SQL), and you cannot access the context variables (like new) in that way. You would need to pass the required columns from new explicitly as parameter values to execute statement, and you could try if you can get it to work by wrapping the PSQL you try to execute dynamically in an execute block, but that would turn ugly pretty fast.

I suggest you rethink what you're doing, and maybe write the real logic in a trigger, instead of trying to do it fully dynamic like you're trying to do now.

Upvotes: 2

Doug Filteau
Doug Filteau

Reputation: 1

Here's my example based on Mark's example above and working in Firebird 3.0:

SET TERM ^ ;
CREATE TRIGGER UMCDB_DRIVER_BI FOR UMCDB_DRIVER ACTIVE
BEFORE INSERT POSITION 0
AS 
BEGIN 
    new.ID = gen_id(GEN_DRIVER, 1);
    new.CREATE_USERID = CURRENT_USER;
    new.CREATE_TS = CURRENT_TIMESTAMP;
    -- Get the hashed value for the driver name.
    execute procedure PRC_NAME_HASH( new.DRVR_FIRST_NAME, new.DRVR_MID_INITS, new.DRVR_LAST_NAME)
        returning_values new.NAME_HASH;
END^
SET TERM ; ^

Works beautifully! Thanks, Mark!

Upvotes: 0

Related Questions