Raise Error

[Chintu]

I am trying to raise a simple error in a trigger...But apart from my
error it also displays two other errors.
ORA - 6512 at line 10
and ORA 04088 : error during execution of trigger

My trigger code is

CREATE OR REPLACE TRIGGER CBS_OWNR.BATCHJOBS_TRIG_CHKGROUPNAME

BEFORE INSERT OR UPDATE OF TOGROUP ON
CBS_OWNR.BILLINGBATCH_BATCHJOBS
FOR EACH ROW WHEN (NEW.TOgroup IS NOT NULL)
DECLARE
NUM_RECS NUMBER;
FINAL_GROUP VARCHAR2(30);
E_INVALID_GROUP EXCEPTION;
BEGIN
SELECT COUNT(*) INTO NUM_RECS FROM CBS_OWNR.BILLINGBATCH_EMAILGROUPS
WHERE GROUPNAME=:NEW.TOgroup; --line 10
IF NUM_RECS = 0 THEN

:NEW.TOgroup:=NULL;
RAISE_APPLICATION_ERROR(-20901, 'Group Does not Exist');
END IF;

END;

[steph]

What do you want to achieve? From what I see you seem to want to set
column TOGROUP to null. But as you raise an exception afterwards, this
will never happen. Regarding the other error messages: these are
subsequent errors to error -20901.

br,
stephan

[Chintu]

> stephan- Hide quoted text -
>
> - Show quoted text -

Yes if entered value in the ToGroup is not in the Emailgroups Table
then set the value to null and and also display a message or error
about why it has been changed to null.

Thanks
Chintu

[steph]

It won't work that way. Maybe you should keep the information about
the reason for setting it to null in a log-field.

[Chintu]

> the reason for setting it to null in a log-field.- Hide quoted text -

>
> - Show quoted text -

Does that mean that when I use the Raise_application_error...it will
not set the ToGroup to null...basically it rollbacks the previous
statement..??
otherwise the fact that it is showing three messages ....is that
fine ??

chintu

[steph]

Yes, exactly.