Re: simple trigger problem

Philippe wrote:

> When the trigger raise an error the transaction is rollbacked (including
> your insert).
>
> yliu_at_creighton.edu wrote in message ...
> >Hi,
> >
> >I was wondering if somebody please help me with the following creating
> >trigger problem: I have a table called yongge (name varchar2(50), age
> >number(3)). I want to validate the age field when I insert/update on the
> >table. Therefore, I created a trigger as follows:
> >
> >create or replace trigger check_age
> >before insert or update on yongge
> >for each row
> >declare
> > low_age constant number :=0;
> > hi_age constant number :=200;
> >begin
> > if (:new.age > hi_age or :new.age < low_age) then
> > insert into error_log values(sysdate, 'Age out of range');
> > raise_application_error(-20500, 'Age out of range');
> > end if;
> >end;
> >/
> >
> >The above trigger was created without error. However, every time when I
> >try to insert a age value which is out of range, I could not find my
> >corresponding entry in my error_log table.
> >
> >Any help would be appreciated.
> >
> >Best regards,
> >yliu_at_creighton.edu
> >

And so, what you could do would be to just raise the exception in the trigger, which
would bounce the insert. Then, in your calling PL/SQL block, you could use a PRAMGA to define an exception basesd on your user error, and do the insert to your audit trail as exception handling, as follows...

DECLARE
...
PRAGMA EXCEPTION_INIT( age_out_of_range, -20500 ); ...
BEGIN
...
  INSERT INTO yongge(...);
...
EXCEPTION
  WHEN age_out_of_range THEN
    INSERT INTO error_log_values (...); END; Hope this helps,

--
Any opinions expressed here are my own, not those of Oracle Corporation.

Steven Chandler
Oracle DoD Consulting, Eastern Region Received on Fri Jul 23 1999 - 08:15:06 CDT