Before trigger processing multiple rows and just the current one
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Before trigger processing multiple rows and just the current one

Threaded View

  1. #1
    Join Date
    Jul 2003
    Posts
    131

    Question Row level before trigger processing multiple rows and not just the current one

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE 11.2.0.1.0 Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

    ------------------
    What am I doing wrong. Why it is inserting 3 records when it should only 1)? Please advice. Thanks
    -----------------


    I have a before trigger that is processing (inserting in this case) multiple previous rows along with the current one. To debug, I made it very basic one but it is still doing the same thing.

    Z_table.message_id is the primary key.


    Please see the trigger code below:
    ------------------


    create table Z_table_copy as select * from z_table where 1=2;

    truncate table Z_table_copy ;


    create or replace trigger tr_new
    before insert or update or delete
    on z_table
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    when ( NEW.group_code = 'abc')
    declare

    v_group_id integer;

    begin

    Insert into Z_table_copy (message_id, change_id, group_code, comments)
    Values(:NEW.message_id, :NEW.change_id, :NEW.group_code, :NEW.comments);


    end;


    ---------------------------------

    insert into Z_table Values ('aa', 1, 'abc', 'mycomment')

    Select count(*) from Z_table_copy ;

    count(*)
    ----------
    3
    Last edited by daljitsb; 07-25-2011 at 04:31 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width