Re: Problem - Trigger

DC,

You need to add a CHECK CONSTRAINT as follows ALTER TABLE DEPT
 ADD CONSTRAINT CHK_DEPT_DEPTNO
 CHECK (
deptno < 10
 );

If a user enters a value > 10 then ORA-02290 will be raised which you can catch in your EXCEPTIONS.

Chris

DC wrote:
>
> 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 - 02:26:49 CST