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

Hybrid 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.

  2. #2
    Join Date
    Jul 2003
    Posts
    131
    Looks like some kid of Oracle Bug that caused row level trigger to get confused.

    The trigger was originally PRAGMA AUTONOMOUS_TRANSACTION
    and there commits and rollbacks within trigger and called-in stored proc. That probably confused the trigger or database.

    It was processing multiple previous table rows inserts along with the current row getting inserted even when I removed AUTONOMOUS_TRANSACTION part from the trigger.

    Dropping and recreating the same trigger was not even fixing the issue but then deleting those rows and creating very basic new trigger eventually resolved the issue.

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