Firebird 3 trigger syntax
Karol Bieniaszewski
Hi
I use Firebird 3 and have problem with trigger
Inside it i have:
IF (VAR_P_ID IS NULL) THEN
BEGIN
VAR_WAS_PUNKT = 'F';
VAR_P_ID = GEN_ID(GEN_PUNKT__ID, 1);
INSERT INTO PUNKT (ID, WOJ_ID, POW_ID, GMI_ID, MIEJSCOWOSC, POCZTA, KOD_POCZTOWY,
UL_SKROT, ULICA, NR_DOM, KRAJ, DT_INS, KIEDY, ZRODLO_INS, ZRODLO_MOD)
VALUES (
:VAR_P_ID,
:NEW.WOJ_ID,
:NEW.POW_ID,
:NEW.GMI_ID,
REMOVE_DOUBLE_CHAR(TRIM(:NEW.MIEJSCE_MIEJSCOW)),
REMOVE_DOUBLE_CHAR(TRIM(:NEW.MIEJSCE_POCZTA)),
:NEW.MIEJSCE_KOD_POCZT,
:NEW.MIEJSCE_UL_SKROT,
REMOVE_DOUBLE_CHAR(TRIM(:NEW.MIEJSCE_ULICA)),
REMOVE_DOUBLE_CHAR(TRIM(:NEW.MIEJSCE_NR_DOM)),
COALESCE(NEW.MIEJSCE_KRAJ, 'PL'),
'now',
'now',
'k',
'k'
);
WHEN SQLCODE -803 DO
BEGIN
VAR_P_ID = NULL;
END
NEW.PUNKT_ID = VAR_P_ID;
END
I got an error:
SQL Message : -104
Invalid token
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 345, column 6
NEW
When i remove
WHEN SQLCODE -803 DO
BEGIN
VAR_P_ID = NULL;
END
All is ok. How to fix syntax?
Regards,
Karol Bieniaszewski
Svein Erling Tysvær
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4VQ5kn1QzWz20db%40smtp.poczta.onet.pl.
Karol Bieniaszewski
Interesting that when i surround it with BEGIN END all is ok, strange, but fixed the problem..
IF (VAR_P_ID IS NULL) THEN
BEGIN
VAR_WAS_PUNKT = 'F';
VAR_P_ID = GEN_ID(GEN_PUNKT__ID, 1);
BEGIN
Regards,
Karol Bieniaszewski
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAKC2LgxWDCi5tduH98b1NOEmRsiKEA3HOYuusJhfe3JGY3vXcw%40mail.gmail.com.
Pavel Cisar
Dne 25. 04. 24 v 9:35 'Karol Bieniaszewski' via firebird-support napsal(a):
> but fixed the problem..
MUST be at the end of code block (begin ... end). It could be followed
only by another WHEN clause. But you put further code at the same block
level after WHEN clause, hence the parser did not recognized it as
valid. Sure, the error message was misleading, and not pointing to real
cause.
The easiest way to understand it is looking at it like TRY .. EXCEPT in
other languages. The the BEGIN is like TRY, and the WHEN is like EXCEPT,
i.e.
BEGIN -- TRY
... -- guarded code
WHEN ... DO -- EXCEPT
END -- end of guarded block
There is nothing like FINALLY or ELSE parts in PSQL.
See
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref30/fblangref30-psql-handleexceptions.html
best regards
Pavel Cisar
IBPhoenix
Vlad Khorsun
| The WHEN … DO block must be located at the very end of a block of statements, before the block’s END statement.
Karol Bieniaszewski
Thank you Vlad
Then the syntax provided is a little misleading. Ok, description below described it clearly and it is ok, but:
Currently it is:
WHEN {<error> [, <error> ...] | ANY}
DO <compound_statement>
<error> ::=
{ EXCEPTION exception_name
| SQLCODE number
| GDSCODE errcode
| SQLSTATE sqlstate_code }
But i suppose it should be, as the description say it explicitly thet after should be the END
BEGIN /* any BEGIN even trigger body BEGIN */
STATEMENT
WHEN {<error> [, <error> ...] | ANY}
DO <compound_statement>
<error> ::=
{ EXCEPTION exception_name
| SQLCODE number
| GDSCODE errcode
| SQLSTATE sqlstate_code }
END /* any END even trigger body END */
Regards,
Karol Bieniaszewski
Od: Vlad Khorsun
Wysłano: czwartek, 25 kwietnia 2024 10:25
Do: firebird-support
Temat: Re: [firebird-support] Firebird 3 trigger syntax
If I understand you correctly, you have something like
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/dad415e8-b626-40e3-9f8f-207c31c25ee0n%40googlegroups.com.
Karol Bieniaszewski
Good explanation, thank you 😊
Regards,
Karol Bieniaszewski
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3e00716a-0460-4efb-bd1d-31659f171cc9%40gmail.com.
Mark Rotteveel
> Then the syntax provided is a little misleading. Ok, description below
> described it clearly and it is ok, but:
doesn't document the wider syntax. That said, the current documentation
is not ideal in this regard. I'll see if I can improve it.
> Currently it is:
>
> WHEN {<error> [, <error> ...] | ANY}
> DO <compound_statement>
>
> <error> ::=
> { EXCEPTION exception_name
> | SQLCODE number
> | GDSCODE errcode
> | SQLSTATE sqlstate_code }
>
> But i suppose it should be, as the description say it explicitly thet
> after should be the END
>
> BEGIN /* any BEGIN even trigger body BEGIN */
>
> STATEMENT
>
> WHEN {<error> [, <error> ...] | ANY}
> DO <compound_statement>
>
> <error> ::=
> { EXCEPTION exception_name
> | SQLCODE number
> | GDSCODE errcode
> | SQLSTATE sqlstate_code }
>
> END /* any END even trigger body END */
WHEN ... DO documentation.
Also, with the syntax you suggest, you'd only be able to have *one* and
only *one* WHEN block, which is not how it works.
That is, in the documentation of BEGIN ... END, it should probably
document it like:
<block> ::=
BEGIN
[<compound_statement> ...]
[<when-do> ...]
END
<compound_statement> ::= {<block> | <statement>}
Where <when-do> refers to the WHEN ... DO documentation, and maybe
repeat this in the section of WHEN ... DO.
Mark
--
Mark Rotteveel