Re: Question on Insert Trigger

zigzagdna@xxxxxxxxx wrote:
On Mar 7, 7:42 am, zigzag...@xxxxxxxxx wrote:
On Mar 7, 12:58 am, DA Morgan <damor...@xxxxxxxxx> wrote:





zigzag...@xxxxxxxxx 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...@xxxxxxxxxxxxxxxx (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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: Question on Insert Trigger
    ... conditions for filtering are more complex. ... after INSERT ON employee ... Dbms_output before rollback is exceuted, then rollback is exceuted, ... What I am asking is there a way to do filtering in the trigger if it ...
    (comp.databases.oracle.server)
  • Re: Question on Insert Trigger
    ... after INSERT ON employee ... Dbms_output before rollback is exceuted, then rollback is exceuted, ... What I am asking is there a way to do filtering in the trigger if it ...
    (comp.databases.oracle.server)
  • Re: Question on Insert Trigger
    ... I wrote a trigger like following. ... I was hoping that when I do rollback that row will not be inserted. ... Why not just put a NOT NULL constraint on the column? ... Puget Sound Oracle Users Group ...
    (comp.databases.oracle.server)
  • Re: Getting trigger error
    ... >that has already being done on the store procedure or on the trigger? ... It will ROLLBACK the changes you ... > Thankes Roji for your attanction ... P. Thomas" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem bei InsteadOfInsert-Trigger
    ... Wann immer möglich sollte man IMO auf Trigger (INSTEAD OF ... innerhalb von Triggern keine ROLLBACK ... DECLARE @ErrorMessage NVARCHAR; ... -- execution to jump to the CATCH block. ...
    (microsoft.public.de.sqlserver)