AFTER INSERT trigger breaks index

[Anonymous]

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]

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]

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