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

Home -> Community -> Usenet -> c.d.o.misc -> DB level Trigger

DB level Trigger

From: DC <him_at_tra-la-la.com>
Date: Wed, 27 Jan 1999 20:52:48 -0500
Message-ID: <36AFC2F0.4463FBE0@tra-la-la.com>


HI;

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


Received on Wed Jan 27 1999 - 19:52:48 CST

Original text of this message

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