Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> DB level Trigger
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