Re: Question on Insert Trigger

From: DA Morgan <>
Date: Thu, 06 Mar 2008 21:58:09 -0800
Message-ID: <> wrote:
> I am using on HP UNIX 11i.
> I have a third party application where I do not have access to code.
> When rows are inserted in a table, I do not want certain rows to be
> inserted . Rest of the rows should be inserted. Is it is possible to
> do this using a trigger.
> I wrote a trigger like following. This trigger is for illustrative
> purposes; conditions for filtering are more complex.
> create or replace trigger emp_aftins
> after INSERT ON employee
> for each row
> declare
> if ( is null) then
> dbms_output.put_line(:new.wwid || 'name' ||;
> rollback;
> else
> commit;
> end if;
> END;
> /
> I was hoping that when I do rollback that row will not be inserted. I
> found that
> Dbms_output before rollback is exceuted, then rollback is exceuted,
> yet row is being inserted.
> If I try to delete a row instead of rollback, I get mutating table
> error.
> Appreciate any help.
> Thanks a lot.

All that work for nothing. <g>

Why not just put a NOT NULL constraint on the column? Then use the EXCEPTIONS INTO to trap the violating rows.

You will find a demo here for a PK but it works the same for a check constraint.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Thu Mar 06 2008 - 23:58:09 CST

Original text of this message