Thanks Helen,
Will try it Tuesday. Apologies for the errors......as typing from memory,
and the fingers typed pascal....and flaky keywords.

regards
John

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <firebird-support@yahoogroups.com>
Sent: Sunday, April 24, 2005 11:06 AM
Subject: Re: [firebird-support] Reading GDSCODE inside a Trigger - FB1.52


>
> At 10:05 AM 24/04/2005 +1000, you wrote:
>
>>Hi All,
>>Is it possible to obtain the value of the GDSCODE when an exception occurs
>>within a trigger without explicitly using "when GDS_ERRNO....." syntax?
>>Somtimes I need to catch all GDS errors and log.
>>
>>Background:
>>Last week I had a situation where an insert was failing. Investigating
>>showed one of the tables triggers was generating an SQLCODE = -901.
>>This error can caused be many (>100, I lost count) GDS errors.
>>
>>I am logging errors such as this to a table using the following structure
>>within my triggers:
>>
>>begin
>> for select......
>>...
>>...
>> where any begin
>> insert into error_log (SQLERR, GDSERR, stamp) values (SQLCODE,
>> GDSCODE, current_datetime);
>> end;
>>end;
>>
>>As documented SQLCODE returns the errror correctly, however, as also
>>documented GDSCODE returns 0, which doesnt help to isolate the cause of
>>the error, when the exact GDSCODE is unknown.
>
> You can have the sqlcode or the gdscode, but not both. If you need the
> gdscode, go for the gdscode, as each gdscode hangs off one and only one
> sqlcode. (You can get the groupings from the download file in the User
> Documentation category of the Knowledgebase section at the main Firebird
> website).
>
> Modified handler code (watch out for your flaky keyword syntax and
> terminators!! this is PSQL, not Pascal)
>
> set term ^^;
>
> begin
> for select......
> ...
> ...
> WHEN any DO
> begin
> IF (GDSCODE IS NOT NULL AND GDSCODE > 0) THEN
> insert into error_log (GDSERR, stamp)
> values (GDSCODE, current_TIMESTAMP);
> end /* ; */
> end ^^
>
> Reason for null test is that WHEN ANY is a catch-all and custom exceptions
> don't have either a gdscode or an sqlcode.
>
> You might also want to look at the idea of saving the gdscode into a local
> variable and handing the internal exception on to a custom exception, so
> that you can also log info about the record that caused the exception,
> e.g.
> the primary key value. If you do this, be meticulous about
> re-initialising
> your variable!!
>
> ./hb
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>