AFTER INSERT trigger breaks index

Help
Anonymous
2012-03-31
2014-01-19
  • Anonymous - 2012-03-31

    I have included statements which reproduce the problem:

    I have an AFTER INSERT trigger on a table which inserts a second "companion" row into the same table, then modifies the original insert, making it reference the companion row.

    This seems to work, except that it renders the index incorrect.  A "SELECT WHERE" is missing the row even though a "SELECT" of the whole table shows the row is there.

    CREATE TABLE index_trigger_bug (
      id int identity,
      name varchar(64),
      companionid int,
    );

    CREATE INDEX index_will_break ON index_trigger_bug (companionid);

    CREATE TRIGGER trigger_breaks_index
    AFTER INSERT ON index_trigger_bug
    REFERENCING NEW ROW AS newrow
    FOR EACH ROW
    BEGIN ATOMIC
    IF newrow.name NOT LIKE 'with%' THEN
    MERGE INTO index_trigger_bug
    USING (VALUES('with ' + newrow.name)) AS source(name)
    ON index_trigger_bug.name = source.name
    WHEN NOT MATCHED THEN INSERT (name) VALUES (source.name);
    SET newrow.companionid = IDENTITY();
    END IF;
    END

    INSERT INTO index_trigger_bug (name)
    VALUES ( ('one'), ('two'), ('three') )

    INSERT INTO index_trigger_bug (name)
    VALUES ( ('four'), ('five'), ('six') )

    • The following select shows that companionid = 9
      select * from index_trigger_bug where id = 6

    • The following select should show the same row, but does not
      select * from index_trigger_bug where companionid = 9

    • The following select, which circumvents the index shows the row again:
      select * from index_trigger_bug where ('X' + companionid) = 'X9'

     
  • Fred Toussi

    Fred Toussi - 2012-03-31

    The NEW ROW is not alterable in AFTER triggers. You can alter it in BEFORE triggers

    This usage is not supported and we will disallow the "SET newrow.companionid = IDENTITY();" when we review the code.

    What you see is caused by the column value being modified in the row without the row being indexed after this modification.

    It is possible to UPDATE the table with an UPDATE statement and assign the new value to the companionid column.

     
  • Fred Toussi

    Fred Toussi - 2012-04-03

    Change committed to SVN to prevent modification of the NEW ROW in AFTER triggers.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks