Re: Question on Insert Trigger

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 07 Mar 2008 10:34:21 -0800
Message-ID: <1204914860.46283@bubbleator.drizzle.com>

zigzagdna_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-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;
> /

This assumes the record has already been committed before the autonomous transaction is called.

Seems remarkably inefficient to me.

Insert bad data and then run a separate transaction to remove it? Would you code a foreign key constraint that way?

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Mar 07 2008 - 12:34:21 CST