Re: Question on Insert Trigger

On 7 mar, 14:07, zigzag..._at_yahoo.com wrote:
> 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-Hidequoted 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;
> /

You have to understand what an autonomous transaction is (and the isolation model: look for 'read commited') before trying to implemement whatever is what you want to implement.

Cheers.

Carlos. Received on Fri Mar 07 2008 - 07:11:36 CST