Firebird Trigger SQL Validate Query

Question

Relatively simple question. I'm trying to perform a statement and check a value prior to adjusting an INSERT value on a firebird trigger. I've seen many examples whereby the new value is assessed and then subsequent statements are executed but none as I want them. Easier to show...

SET TERM ^ ;
CREATE TRIGGER STOP_PREMIX_INSERT_PRODUCEDD FOR PRODUCEDD ACTIVE
BEFORE INSERT OR UPDATE POSITION 0
AS 
BEGIN
    IF ('SELECT COUNT(*) FROM RECIPESTEPS rs INNER JOIN RECIPES r on r.RECIPEID=rs.RECIPEID INNER JOIN PRODUCEDH h on h.RECIPEID=r.RECIPEID WHERE h.BATCHID=' || new.BATCHID || ' AND ' || new.SEQUENCENO || '=rs.SEQUENCENO AND ' || new.COMMODITYID || '=rs.COMMODITYID AND rs.NONWEIGHED=17;' > 0) then
    new.BATCHID=-1;
END^
SET TERM ; ^

I'm trying to validate the existence of a piece of data in another table prior to performing a value change on the new entry.

Normally IF (new.ID = 0) then execute statement 'blah'; would work, but when I write this trigger, it is syntactically accepted but when a value changes I get the following error message:

arithmetic exception, numeric overflow, or string truncation string right truncation At trigger 'STOP_PREMIX_INSERT_PRODUCECEDD' line: 6, col: 5

I might be a semi colon off or this might just not be possible, either way, any help is appreciated.

Not that I know Firebird, but it looks like you're checking if an SQL string is greater than zero. Maybe you need to execute the string and compare the result? — Joachim Isaksson
– Joachim Isaksson, Commented Mar 9, 2016 at 19:21

Mark Rotteveel · Accepted Answer · 2016-03-10 09:35:37Z

2

The reason it doesn't work is because you are constructing a string and then compare it to an integer. The resulting conversion fails. The fact the string contains something that looks like SQL, doesn't actually execute it.

Instead you need to execute a query, for example using EXISTS:

SET TERM ^ ;
CREATE TRIGGER STOP_PREMIX_INSERT_PRODUCEDD FOR PRODUCEDD ACTIVE
BEFORE INSERT OR UPDATE POSITION 0
AS 
BEGIN
    IF (EXISTS (SELECT * 
                FROM RECIPESTEPS rs 
                INNER JOIN RECIPES r on r.RECIPEID=rs.RECIPEID 
                INNER JOIN PRODUCEDH h on h.RECIPEID=r.RECIPEID 
                WHERE h.BATCHID = new.BATCHID 
                AND rs.SEQUENCENO = new.SEQUENCENO
                AND rs.COMMODITYID = new.COMMODITYID
                AND rs.NONWEIGHED = 17)) then
      new.BATCHID=-1;
END^
SET TERM ; ^

This is likely more performant than a select count(*) as in the other answer because Firebird won't need to query all rows, just check that there is one or more.

answered Mar 10, 2016 at 9:35

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MxMave Over a year ago

Thanks for the reply. I tried this route originally but unfortunately Firebird doesn't support the EXISTS clause it seems, hence why i went down the COUNT route.

Mark Rotteveel Over a year ago

@MxMave I'm not sure what you mean, Firebird does support exists, and also in PSQL as displayed above.

MxMave Over a year ago

Apologies, you're correct. I was getting unknown term EXISTS at one stage, it must have been how I structured it that was causing the issue. This executes correctly.

Maxim Votyakov · Accepted Answer · 2016-03-10 01:48:45Z

2

It should be like this:

SET TERM ^ ;
CREATE TRIGGER STOP_PREMIX_INSERT_PRODUCEDD FOR PRODUCEDD ACTIVE
BEFORE INSERT OR UPDATE POSITION 0
AS 
  declare variable CNT integer;
BEGIN
    SELECT COUNT(*) FROM RECIPESTEPS rs INNER JOIN RECIPES r on r.RECIPEID=rs.RECIPEID INNER JOIN PRODUCEDH h on h.RECIPEID=r.RECIPEID WHERE h.BATCHID = new.BATCHID AND new.SEQUENCENO = rs.SEQUENCENO AND  new.COMMODITYID = rs.COMMODITYID AND rs.NONWEIGHED=17 into :CNT;
    IF (CNT > 0) then
        new.BATCHID=-1;
END^
SET TERM ; ^

I cannot check whether it will work because I don't have your database but now you should see the way how to do it right.

answered Mar 10, 2016 at 1:48

Maxim Votyakov

7245 silver badges10 bronze badges

1 Comment

MxMave Over a year ago

Perfect. I had tried similar to this but I was trying to do CNT = () rather than into :CNT. Thanks a bunch!

Collectives™ on Stack Overflow

Firebird Trigger SQL Validate Query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related