At 03:06 PM 2/12/2003 +0000, you wrote:

>Hello,
>
>I'm new in Firebird World and I've some problem with error handling
>in trigger.
>
>I've got this Oracle trigger and I want the same for Firebird. How
>can I translate it :
>
>CREATE OR REPLACE TRIGGER "ECOSYS".TAD_TR_KANTON
>AFTER DELETE on TR_KANTON for each row
>
>declare
> numrows NUMBER;
> err_no NUMBER;
> err_msg VARCHAR2(255);
> err_appl EXCEPTION;
>
>begin
>
> select count(*) into numrows
> from TR_AMT
> where
> TR_AMT.AMT_KAN_ID = :old.KAN_ID;
> if (numrows > 0)
> then
> err_no := -20004;
> err_msg := '[TR_KANTON;KAN_ID;TR_AMT;AMT_KAN_ID]';
> raise err_appl;
> end if;
>
> select count(*) into numrows
> from TR_ORT
> where
> TR_ORT.ORT_KAN_ID = :old.KAN_ID;
> if (numrows > 0)
> then
> err_no := -20004;
> err_msg := '[TR_KANTON;KAN_ID;TR_ORT;ORT_KAN_ID]';
> raise err_appl;
> end if;
>
> EXCEPTION
> when err_appl then
> raise_application_error(err_no, err_msg);
> when OTHERS then
> RAISE;
>
>end;

In Firebird, you pre-create EXCEPTION objects in the metadata:

CREATE EXCEPTION ERR_APPL 'Optional text 78 characters or less';

I don't know the significance of your err_no variable, but you could do

CREATE EXCEPTION ERR_APPL '-20004';

Also, in Firebird, it's very inefficient to verify set existence by using
SELECT COUNT(*). Use the EXISTS() predicate, or another existential
predicate if appropriate.

In Firebird 1.5 you can append text to an exception message - for details,
see the release notes. Your trigger will translate something along these
lines - this is a guideline only, you need to study Firebird's PSQL
language yourself. Trigger languages are NOT standard across DBMSs.

SET TERM ^; /* See note at bottom */

/* "ECOSYS".TAD_TR_KANTON isn't a valid trigger name */
CREATE OR REPLACE TRIGGER TAD_TR_KANTON FOR TR_KANTON
ACTIVE AFTER DELETE POSITION 0 /* you can have multiple triggers */
AS

declare
/* numrows NUMBER; */
/* err_no NUMBER; */
err_msg VARCHAR2(255);
/* err_appl EXCEPTION; */

begin
err_msg = 'Unspecified exception';
if (exists (select 1 from TR_AMT
where AMT_KAN_ID = :old.KAN_ID)) then
begin
err_msg := '[TR_KANTON;KAN_ID;TR_AMT;AMT_KAN_ID]';
/* actually, you can deliver the actual values in the string if you
want */
EXCEPTION ERR_APPL err_msg;
end
if (exists (select 1 from TR_ORT
where ORT_KAN_ID = :old.KAN_ID)) then
begin
err_msg := '[TR_KANTON;KAN_ID;TR_ORT;ORT_KAN_ID]';
EXCEPTION ERR_APPL err_msg;
end
............
/* This is enough if all you want to do is raise the exception. If you
want to handle the exception within the trigger, e.g. swallow it, then you
can write exception handling code using a WHEN...DO block preceding the END
of the block where you want the handling to occur */
WHEN EXCEPTION ERR_APPL DO
begin
Blah...
....
EXCEPTION; /* if you want to re-raise the exception */
end
WHEN GDSCODE....DO
begin
....
end
WHEN SQLCODE....DO
begin
....
end
WHEN ANY DO /* WHEN ANY...DO is like OTHERS */
begin
....
end
end ^ /* notice the use of an alternative terminator when defining
PSQL. Read up on the SET TERM command */
SET TERM ;^

/heLen