More about error management

[fabiano]

I required how to manage error messages and marco kalter's reply was very good (use of Session.MessageTable).
My problem now is how to show proper message when an error occurs in a trigger.

My standard trigger code is similar to:

CREATE OR REPLACE TRIGGER MyTrigger
BEFORE INSERT ON MyTable
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
MyError Exception;
N Number;
BEGIN
SELECT Count(*) INTO N
FROM MyTable T
WHERE :NEW.ID_UI=T.ID_UI AND
T.ANNO=:NEW.ANNO;
if N=0 then
RAISE MyError;
end if;
END;

But i don't know how to display proper message when trigger error raises.
Any suggestions (also how can I change triggers to raise an arror that can be intercepted through MessageTable).

Fabiano

 

[Marco Kalter]

Your trigger should probably also use the same MessageTable to provide an error message. Instead of raising a user defined exception, you could call the raise_application_error procedure with a user defined error code (20000 .. 20999) and the error message retrieved from this table. Such an error will be displayed properly when it occurs during an insert, update or delete operation from a TOracleDataSet.

------------------
Marco Kalter
Allround Automations

 

[fabiano]

Thanks a lot.
Now I can manage good error messaging in my applications!

Fabiano

Originally posted by mkalter:
Your trigger should probably also use the same MessageTable to provide an error message. Instead of raising a user defined exception, you could call the raise_application_error procedure with a user defined error code (20000 .. 20999) and the error message retrieved from this table. Such an error will be displayed properly when it occurs during an insert, update or delete operation from a TOracleDataSet.