Re: Question on Insert Trigger

On Mar 7, 12:58 am, DA Morgan <damor..._at_psoug.org> wrote:
> zigzag..._at_yahoo.com wrote:
> > I am using 10.2.0.3 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
> > PRAGMA AUTONOMOUS_TRANSACTION;
> > BEGIN
>
> > if (:new.name is null) then
> >    dbms_output.put_line(:new.wwid || 'name' || :new.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.http://www.psoug.org/reference/constraints.html#cpc
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Thanks to all. not null in my code was just an example. My filtering logic is lot more complex and cannot implemented through a costraint. What I am asking is there a way to do filtering in the trigger if it cannot be done using EXCEPTIONS INTO statement. Received on Fri Mar 07 2008 - 06:42:46 CST