Re: Question on Insert Trigger

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Fri, 7 Mar 2008 01:38:59 -0800 (PST)
Message-ID: <d6995992-93db-42ef-b9b5-9867ad703034@q78g2000hsh.googlegroups.com>


On 6 mar, 22:48, 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.

I cannot see the point on issuing a commit or rollback inside an autonomous transaction with no DDL inside.

Cheers.

Carlos. Received on Fri Mar 07 2008 - 03:38:59 CST

Original text of this message