Re: Problem Raise_application_error in Trigger

Arijit Chatterjee wrote:
>
> Dear All,
> I write a trigger on windows platform code is all follows.
> ========================================================
> SQL> desc accountmaster;
> Name Null? Type
> ----------------------------------------- -------- ----------------------------
> ACCOUNTCODE NOT NULL NUMBER(10)
> NAMEOFACCOUNT VARCHAR2(100)
> SUPERCODE NUMBER(10)
> OPENINGAMOUNT NUMBER(10,2)
> ACCOUNTGROUP VARCHAR2(20)
> SUBLEDGER CHAR(1)
> PAN VARCHAR2(20)
> ADDRESS1 VARCHAR2(70)
> ADDRESS2 VARCHAR2(70)
> ADDRESS3 VARCHAR2(70)
> CREDITLIMIT NUMBER(10,2)
> CREDITDAYS NUMBER(10)
> DOC VARCHAR2(10)
> ========================================================
> Trigger Definition
> ========================================================
> CREATE OR REPLACE TRIGGER SCOTT.TRG_ACCOUNTMASTER_OPAMT
> BEFORE INSERT OR
> UPDATE OF OPENINGAMOUNT
> ON ACCOUNTMASTER
> FOR EACH ROW WHEN (NEW.SUBLEDGER='T'
> AND NEW.OPENINGAMOUNT<>0)
> BEGIN
> raise_application_error(-20001, ' OPENINGAMOUNT Error.', TRUE);
> END;
> ========================================================
> Now when I giving insert statement
> ========================================================
> SQL> insert into accountmaster (OPENINGAMOUNT,SUBLEDGER) values (1000,'T');
> insert into accountmaster (OPENINGAMOUNT,SUBLEDGER) values (1000,'T')
> *
> ERROR at line 1:
> ORA-20001: OPENINGAMOUNT Error.------------------>[ I need only this line ]
> ORA-06512: at "SCOTT.TRG_ACCOUNTMASTER_OPAMT", line 2
> ORA-04088: error during execution of trigger 'SCOTT.TRG_ACCOUNTMASTER_OPAMT'
> ========================================================
> Now suggest me hot to remove these lines or is there some other way around????
>
> ERROR at line 1:
> ORA-06512: at "SCOTT.TRG_ACCOUNTMASTER_OPAMT", line 2
> ORA-04088: error during execution of trigger 'SCOTT.TRG_ACCOUNTMASTER_OPAMT'
>
> regards
> Arijit Chatterjee

Presumably you will have an application that will catch this error ? In this case, you can format the returned error message however you choose.

btw - you could probably perform the same validation with a check constraint which will be more performant

hth

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------