Re: Question on Insert Trigger

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


.

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, ...
    (comp.databases.oracle.server)
  • Re: Question on Insert Trigger
    ... I wrote a trigger like following. ... after INSERT ON employee ... I was hoping that when I do rollback that row will not be inserted. ... Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text - ...
    (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: 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: Validating ComboBox Entries
    ... you are filtering out expired contracts. ... The Combo Control defualts to a RowSource of: ... The employee can work on the project up to and including the ... from the list display, since it has passed its expiry date. ...
    (microsoft.public.access.formscoding)