Triggers & Rollback

From: Henry Miller <uwda_at_yahoo.com>
Date: 8 Apr 2002 15:24:29 -0700
Message-ID: <497cda72.0204081424.2c5cb81c@posting.google.com>

I have an BEFORE INSERT trigger that, when a condition is met, I alert the user with an RAISE_APPLICATION_ERROR. The problem is when I do that, an automatic rollback happens.

What I'm trying to accomplish is alert the user that something is amiss, but then fix it for them. Any ideas?

  CREATE OR REPLACE TRIGGER trg_foo_bar
    before Insert
    On table_1
    For Each Row   

  Begin
    if :new.hNumber <> 10 then

       raise_application_error(-20101, 'Value not 10!');
       :new.hNumber := 10;

    end if;
  End;

Thanks

J. Received on Mon Apr 08 2002 - 17:24:29 CDT