Adrian,

At 12:43 AM 10/08/2004 -0300, you wrote:

>Hello all
>I'm interested in exception catching, in order to put de exception in a field.
>For example, in a trigger after insert/update/delete the code should be
>
>begin
>...
>when exception e
> new.error = e
> new.usr = current_user
> new.ts = current_timestamp
>end
>
>but this is not possible, in particular with user defined exceptions.
>So the best aproach I find is :
>
>begin
>...
>when gds_code < 0
> new.field_error = gds_code
>when sql_code < 0
> new.field_error = sql_code
>when exception=user_ex_1
> new.field_error = 1 or 'user_ex_1'
>when exception=user_ex_2
> new.field_error = 2 or 'user_ex_2'
>when exception=user_ex_3
> new.field_error = 3 or 'user_ex_3'
>
>when exception=user_ex_N
> new.field_error = N or 'user_ex_N'
>end
>
>where 1 .. N are the exceptions that can raise in [ ... ] block, or all
>the user exceptions.
>
>There is a better solution?
>Why the user exceptions are not reported in a implicit var, like gds_code
>or current_user ?
>Do you feel there is a need for the user_exeption variable, to use in a
>when block?

There seems to be a conceptual problem here. If an exception occurs in a
trigger (which is part of processing an insert, update or delete on a row),
the operation will either fail (the trigger ends and the operation fails)
or the exception is handled by the trigger code in some way that permits
the operation to proceed.

So - if your trigger code tries to handle an exception by plugging some
value into a database field, the cause of the exception (e.g. a key
violation or a lock conflict) is not handled. Nothing that goes into any
database field will be allowed to be posted to the database - and that
includes your error fields, of course.

In PSQL you have quite a lot of ways to handle exceptions. You can always
capture the user name with the CURRENT_USER variable. You can write error
logs to external tables - these will always survive, even though the
exception causes the trigger action to fail or the stored procedure to be
undone.

In Firebird 1.5 and later versions you can
1) capture the SQLCODE or the GDSCODE inside the exception-handling block
2) customise an error message for any exception and optionally re-raise the
exception
3) capture CURRENT_TRANSACTION and CURRENT_CONNECTION

and so on...

/heLen