ID Number: Q80083
1.10 1.11
OS/2
buglist1.10 buglist1.11
Summary:
Problem ID: PRSQL9201001
SYMPTOMS
When a trigger on INSERT or DELETE contains a comparison of the
text field of the inserted or deleted table, and the LIKE operator
is used in the WHERE clause, the INSERT or DELETE operation fails.
For example, inserting in a text column of a table with a trigger
on INSERT results in the following message:
The text table and the table referenced by the text pointer
disagree.
(Msg 7134, Level 16, State 1)
Deleting with the table having the delete trigger results in the
following message:
Invalid text pointer value <0xnnnn...>
(Msg 7123, Level 16, State 1)
CAUSE
SQL Server incorrectly handles the text pointers for the text field
of the inserted and deleted tables. This occurs when an insert or
delete trigger contains a statement that compares the text field of
these tables using the LIKE operator. When you insert or delete
values for the text field, the corresponding trigger is invoked and
causes the insert or delete to fail.
For example:
create table test (col1 text)
create trigger trig1 on test for insert (delete) as
if exists (select col1 from inserted (deleted)
where col1 like 'a' )
<take action>
When an attempt is made to insert
insert test values ('a')
or to delete
delete test where col1 like 'a'
the above messages occur.
WORKAROUND
To compare the values of the text type field in the trigger, insert
the rows from the inserted or deleted tables into another table
with the same definitions as the table on which the trigger is
defined, then make comparisons on that other table, as follows:
create table test (col1 text)
create table temptest (col1 text)
create trigger trig1 on test for insert (delete) as
insert temptest select col1 from inserted
if exists (select col1 from temptest where col1 like <string>)
<take action>
STATUS
Microsoft has confirmed this to be a problem in SQL Server versions
1.1 and 1.11. This problem does not occur in SQL Server version 4.2.
Additional reference words: 1.10 1.11