Hi!

I'm in trouble with the "raise_application_error" message: I made a data validator trigger to give some feedback for users about their mistakes, so the trigger check the length of the data and if it isn't correct I want to promt the user, but when an exception coming, the row (in code below) with "raise_application_error (-20000, 'ROSSZ ADAT:' || tetel);" doesn't work, no message appear in Oracle Forms. It's possible to show a table trigger generated message on Oracle Froms? Can someone help me again?

Thanks and regards: Tamas

The trigger code is:

CREATE OR REPLACE TRIGGER VALIDAL
BEFORE
INSERT OR UPDATE OF CENZURASZAM
ON HP_HIANYPOTLAS
FOR EACH ROW
DECLARE
rosszadat EXCEPTION;
tetel VARCHAR2(15);
BEGIN

IF LENGTH(:NEW.cenzuraszam) != 12
THEN
tetel:='CENZÚRASZÁM';
RAISE rosszadat;
END IF;

EXCEPTION
WHEN rosszadat
THEN
raise_application_error (-20101, 'ROSSZ ADAT:' || tetel);
ROLLBACK;
END;
/

Yes, you can trap and show the error in Forms. One way is to code an ON-ERROR trigger which traps the error and displays DBMS_ERROR_TEXT - look at the documentation for DBMS_ERROR_TEXT for an example.

Actually, your example should not be done using a trigger, as it can be done more efficiently by a check constraint:

ALTER TABLE HP_HIANYPOTLAS ADD CONSTRAINT chk_cenzuraszam
CHECK (LENGTH(:NEW.cenzuraszam) = 12);

Your form would see the DBMS_ERROR_TEXT:
ORA-02290: check constraint (xxx.chk_cenzuraszam) violated

It could then easily parse this and produce a more user-friendly message.

As it stands, your trigger has a ROLLBACK that is redundant and will never happen anyway (since RAISE_APPLICATION_ERROR aborts the process), and could be simplified to:

CREATE OR REPLACE TRIGGER VALIDAL
BEFORE
INSERT OR UPDATE OF CENZURASZAM
ON HP_HIANYPOTLAS
FOR EACH ROW
BEGIN
IF LENGTH(:NEW.cenzuraszam) != 12
THEN
raise_application_error (-20101, 'ROSSZ ADAT:' || 'CENZÚRASZÁM');
END IF;
END;

(But as I said before, a CHECK constraint is better).

Thank you the answer Andrew, another gap filled in my mind.

I'll use the constraints too, and understand the using of DBMS_ERROR_TEXT.

Is some significant difference in cycletime when I use 7 constraint or 1 trigger with 7 if-then to validate fields on form?

Thanks again!
Tamas

I haven't got any figures to back this up, but I believe any differences would be insignificant. However, constraints are generally more efficient than triggers - and remember: a table with 15 NOT NULL columns already has 15 check constraints: i.e. they are not an issue.
The general rule is: always use a constraint rather than a trigger, if possible.

Thank you Andrew i'll keep it in mind!

Tamas