Re: Question on Insert Trigger

"Carlos" <miotromailcarlos_at_netscape.net> schreef in bericht news:d6995992-93db-42ef-b9b5-9867ad703034_at_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.

And if it wasn't autonomous, I would not place a commit in an after insert trigger....
So I think DA is right, use a constraint!

Shakespeare Received on Fri Mar 07 2008 - 05:39:03 CST