CODEWITHSUNDEEP
triggersfirebird
Pajkec
Pajkec

Reputation: 67

Firebird run execute statement inside trigger

I wrote a procedure STRING_SESTAVLJEN_ENAKOST_TABEL('MERILA_STRANKE') that generates part of code that i want to execute (some long if statement) 

        IF ((new.LOKACIJA                        IS DISTINCT FROM old.LOKACIJA                       )
        OR (new.MODIFIED                        IS DISTINCT FROM old.MODIFIED                       )
        OR (new.KARAKTERISTIKE                  IS DISTINCT FROM old.KARAKTERISTIKE                 )
        OR (new.LETNIK                          IS DISTINCT FROM old.LETNIK                         )
        OR (new.ID_PNS_CERT_POS                 IS DISTINCT FROM old.ID_PNS_CERT_POS                )
        OR (new.ID_PNS_CERT_POS                 IS DISTINCT FROM old.ID_PNS_CERT_POS                ))

and I want to call it in trigger, then add some code and run it all together.

The code is: 

    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    

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

             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 ; ^

Now when I run the update and trigger triggers I get this error:

SQL Message : -104 Invalid token

Engine Code : 335544569 Engine Message : Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 1 IF

On the other hand if I write it like this:

    SET TERM ^ ;
    ALTER TRIGGER BI_MERILA_STRANKE ACTIVE
    BEFORE INSERT OR UPDATE POSITION 0
    AS 
    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    

            IF ((new.LOKACIJA                        IS DISTINCT FROM old.LOKACIJA                       )
            OR (new.MODIFIED                        IS DISTINCT FROM old.MODIFIED                       )
            OR (new.KARAKTERISTIKE                  IS DISTINCT FROM old.KARAKTERISTIKE                 )
            OR (new.LETNIK                          IS DISTINCT FROM old.LETNIK                         )
            OR (new.ID_PNS_CERT_POS                 IS DISTINCT FROM old.ID_PNS_CERT_POS                )
            OR (new.ID_PNS_CERT_POS                 IS DISTINCT FROM old.ID_PNS_CERT_POS                ))
            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 ; ^

It works as it should. I do not understand why it doesn't run if is more or less the same code.

Upvotes: 0

Views: 1712

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109162

As I also mentioned in your previous question, execute statement cannot be used to execute snippets of PSQL (procedural SQL) like that, it can only execute normal DSQL (dynamic SQL). And as it doesn't understand PSQL, you get the "token unknown - if" error, because if is not valid in DSQL.

execute statement is equivalent to executing SQL yourself from a query tool or application (it uses the same API), you can't use if there either.

There is a loophole by using an execute block statement, but that still would not allow you to gain access to the NEW (or OLD) trigger context variables unless explicitly passed as parameters, which would negate most of the usefulness of dynamically generated code in this context.

The only real solution is to write the trigger and not do it dynamically, maybe using a code generator (I'm not sure if any exist, otherwise you need to write that yourself).

Upvotes: 3

Related Questions