Re: DB level Trigger

From: rkajda <rkajda_at_ComArch.pl>
Date: Wed, 27 Jan 1999 08:19:16 GMT
Message-ID: <36AECB8D.2DF5CBAA@ComArch.pl>

Hi
You can add on-error trigger in block (dept) level for example something like that: begin

  if( dbms_error_code = -20001 )then

      message( 'Not allowed <or other your message>' );
      bell;

  else
    message( to_char( error_code )||

             error_text );
  end if;
  raise Form_Trigger_Failure;
end;
which allow you catch error end service it.

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:19:16 CST