Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DB level Trigger

Re: DB level Trigger

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Fri, 29 Jan 1999 17:17:31 +0800
Message-ID: <36B17CAB.634F@bhp.com.au>


DC wrote:
>
> I am a student attending a Institute learning Oracle Application
> Developer. I have a question that cannot be answered that I hope you
> are able to answer for me. It deals with creating Database Triggers.
> I have a table called DEPT, when the user enters in a record with a
> value for DEPTNO greater then 10 then I do not want to save that record
> to the database. I also want to display a message to the user that this
> is not possible. By the way I can do this trigger on form level but I
> am having trouble with it on the DATABASE LEVEL. Here is my code.
>
> I did this in Plus 3.3, I know that it also can be done in Procedure
> Builder.
>
> CREATE OR REPLACE TRIGGER MY_TRIGGER
> BEFORE INSERT OR UPDATE ON DEPT
> FOR EACH ROW
> BEGIN
> IF INSERTING THEN
> IF :NEW.DEPTNO > 10 THEN
> RAISE_APPLICATION_ERROR(-20001,'NOT ALLOWED TO SAVE HERE!');
> END IF;
> END IF;
> IF UPDATING THEN
> IF :OLD.DNAME ='SALES' THEN
> RAISE_APPLICATION_ERROR(-20001,'NOT ALLOWED TO SAVE HERE!');
> END IF;
> END IF;
> END;
>
> This is the answer I get when I run it in Oracle Forms. I just created a
> canvas containing a block from the table DEPT with its fields. When I
> run it,and enter a record above 10 and try to save it, it gives me the
> error (:ORACLE error:Unable to INSERT RECORD.) and in the HELP\DISPLAY
> ERROR: this is what shows:
>
> ORA-20001: NOT ALLOWED TO SAVE HERE!
> ORA-06512: at "SCOTT.MY_TRIGGER", line 4
> ORA-04088: error during execution of trigger 'SCOTT.MY_TRIGGER'
>
> Which is what I want to happen, but I believe that there is a different
> way to do this. Plus ORA-04088 show an error saying (error during
> execution of trigger. My instructor says this is correct. IS IT
> I believe that it is better to have some sort of ALERT BOX displayed
> because it is easier to see and understand for the user. If there is a
> better way to write a database trigger like this, I would really
> appreciate an answer.
>
> THANK YOU
> DAN.(student)
>
> Email: dan.ciric_at_excite.com
> ----------------------

Your code is correct...

If you want to "massage" the error message from Forms to give nicer output etc etc...you can try:

  1. playing about with "sqlerrm" which contains the error message (in its entirity)
  2. using the output from dbms_utlity.format_call_stack to determine which part of the error message you want...

etc etc

but your code is doing the right thing

Have fun

--



Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad" Received on Fri Jan 29 1999 - 03:17:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US