rollback transaction based on before update trigger condition

[Giovanni Bucci]

Hello,

I am trying to figure out how to rollback a transaction on a table update or insert based on a condition inside a before update trigger.

All the examples I found do generate an exception message and I am not sure how to proceed, like if the exception itself cause the transaction to fail or if I need to intercept an error status in the db?

as to say i make an update and in turn this fires a before update trigger, in the trigger I test any arbitrary condition, then how do I trap this situation? 

I did try with create exception and i can raise an exception but i do not understand how to trap it as the WHEN SQLCODE has no code for my own generated exception.

Do I need to mandatory generate an exception? Can I generate an exception that has no message error and can be trapped as soon as the trigger returns/exit, inside the code ?

[Dimitry Sibiryakov]

Giovanni Bucci wrote 14.05.2024 13:38:
> I am trying to figure out how to rollback a transaction on a table update or
> insert based on a condition inside a before update trigger.

Give up. Triggers in Firebird cannot control transactions. Only client
applications can.

--
WBR, SD.

[Giovanni Bucci]

ok but i do not want to control the transaction inside the trigger,

I want to do something like this:

1. define an exception myexception 

2. define a trigger that fires before update

3. set transaction in a client...runn the update of a record in a table

4. run the update...the trigger fires..

5. the trigger generate myexception without any message when it is needed

6.the client has something like this to rollback the transaction

  WHEN EXCEPTION myexception DO Rollback;

Does this makes sense? can I do something like WHEN EXCEPTION myexception DO Rollback; or this is not possible? how do I make my own error code for SQLSTATE in FB <= 3.0 ?

[Dimitry Sibiryakov]

Giovanni Bucci wrote 14.05.2024 13:59:
> 6.the client has something like this to rollback the transaction
>   WHEN EXCEPTION myexception DO Rollback;
>
> Does this makes sense? can I do something like WHEN EXCEPTION myexception DO
> Rollback; or this is not possible? how do I make my own error code for SQLSTATE
> in FB <= 3.0 ?

WHEN EXCEPTION as any other PSQL is executed on server, not client.

--
WBR, SD.

[Tomasz Tyrakowski]

On 14.05.2024 at 13:59, Giovanni Bucci wrote:
> ok but i do not want to control the transaction inside the trigger,
>
> I want to do something like this:
>
> 1. define an exception myexception
> 2. define a trigger that fires before update
> 3. set transaction in a client...runn the update of a record in a table
> 4. run the update...the trigger fires..
> 5. the trigger generate myexception without any message when it is needed
> 6.the client has something like this to rollback the transaction
> WHEN EXCEPTION myexception DO Rollback;
>
> Does this makes sense? can I do something like WHEN EXCEPTION myexception
> DO Rollback; or this is not possible? how do I make my own error code for
> SQLSTATE in FB <= 3.0 ?

What is it you call "client"? Is it an application, or are you just
typing queries in an SQL console (isql, FlameRobin, ...). In an
application scenario, you raise an exception in your trigger, the
exception is sent back by Firebird server to your client library, and
it's signaled to your code by whatever client / DB component library you
use, either as an exception (specific to your programming language) or
in another way (e.g. a status / return code). Then your code can decide
what to do next, for example issue a rollback or commit of a transaction
that you previously started.

regards
Tomasz

[Giovanni Bucci]

I am trying now with ISQL to create and exception and  a trigger that raise that exception on a specific condition,
then still from isql i run a transaction to make an update and inside the isql i try to catch the exception and rollback 

the transaction based on the exception the trigger raise.

if it works then i can do this also in a delphi software.

still from  the database experts Doc/How to lock a record in InterBase/Firebird (ibexpert.net)

i am trying to implement this

2. Users are competing for some resource. For example, a sales person might reserve some goods items for some customers in case they run out. The stock issue is recorded before the goods physically move out of stock. The solution is similar to the previous one, except that a Before Update trigger is added. It checks that the stock available is not negative and adjusts the client side logic accordingly. If you are out of goods, there is no reason to continue the operation.

[Dimitry Sibiryakov]

Giovanni Bucci wrote 14.05.2024 14:19:
>
> if it works then i can do this also in a delphi software.

ISQL is an application by yourself and it has completely different purpose
and architecture from your application. You cannot "test everything with isql
and then implement the same in Delphi".
Run directly to Delphi application.

--
WBR, SD.

[Tomasz Tyrakowski]

On 14.05.2024 at 14:19, Giovanni Bucci wrote:
> I am trying now with ISQL to create and exception and a trigger that raise
> that exception on a specific condition,
> then still from isql i run a transaction to make an update and inside the
> isql i try to catch the exception and rollback
> the transaction based on the exception the trigger raise.

In isql you execute START TRANSACTION, then some other statements and
after them either COMMIT or ROLLBACK. If a statement causes an exception
(e.g. one raised by a trigger), you can see the exception directly in
isql output. When an exception appears, you can still execute COMMIT or
ROLLBACK (according to your liking). isql won't do anything
automatically - it's your decision what to do when you see a query
resulting in an error.
In Delphi the exception from the database will be (I assume you're going
to use some kind of DB components library like IBX, IBO or FireDAC)
thrown as Object Pascal exception, and you should use a standard try ...
except construct to detect and handle those exceptions (you can roll
back the transaction in the except clause for example).

regards
Tomasz

[liviuslivius]

In Delphi exception you got in message the name of your exception raised in the trigger.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Giovanni Bucci <g.bu...@gmail.com>

Data: 14.05.2024 14:19 (GMT+01:00)

Do: firebird-support <firebird...@googlegroups.com>

Temat: Re: [firebird-support] rollback transaction based on before update trigger condition

--
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/a0b83763-2fae-4690-9b3f-9ecea7a46696n%40googlegroups.com.

[Giovanni Bucci]

thanks, I realize now how much ignorant I am, I read that SET TRANSACTION and ROLLBACK are available only as  DSQL, ESQL I believe I cannot use it inside a stored procedure, as it is not PSQL is it my understanding correct?

[Dimitry Sibiryakov]

Giovanni Bucci wrote 14.05.2024 14:35:
> I read that SET TRANSACTION and ROLLBACK are available only as DSQL, ESQL I
> believe I cannot use it inside a stored procedure, as it is not PSQL is it my
> understanding correct?

Yes.

But it is more: in your application you MUST NOT use "set transaction" and
"rollback" queries. You must use corresponding calls of API/DB components.

PS: Well, theoretically you can use these SQL queries but it may cause some
problems to your application so better don't.

--
WBR, SD.

[Tomasz Tyrakowski]

On 14.05.2024 at 14:35, Giovanni Bucci wrote:
> thanks, I realize now how much ignorant I am, I read that SET TRANSACTION
> and ROLLBACK are available only as DSQL, ESQL I believe I cannot use it
> inside a stored procedure, as it is not PSQL is it my understanding correct?

Well, in general: yes. With the exception of IN AUTONOMOUS TRANSACTION
construct, which is actually used in PSQL and starts a new transaction
(https://www.firebirdsql.org/refdocs/langrefupd25-psql-autonomous-trans.html).

regards
Tomasz