Re: Question on Insert Trigger

<zigzagdna_at_yahoo.com> schreef in bericht news:77898980-83d7-4593-ac6d-9e0f174ff25b_at_n36g2000hse.googlegroups.com... On Mar 7, 7:42 am, zigzag..._at_yahoo.com wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
I tried following which does delete instead of rollback. It goes thorugh the delete statement, but it still does not delete the row why? wwid is the prmary key.

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);    delete from employee where wwid = :new.wwid; end if;

    commit;

END;
/

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Maybe your autonomous transaction deletes before your original transaction commits?

Shakespeare Received on Fri Mar 07 2008 - 07:13:13 CST