Search code examples
sqlsql-servertriggersmerge-replication

Trigger does not work because of deprecated datatype in replicated table

I have a table in my SQL Server 2005 database, which has a field with the datatype NTEXT. This table is replicated in a merge-replication with a lot of SQLCE-subscriptions.

I have tried to add an ON INSERT trigger to this table to process the NTEXT-field. But I got this error:

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

On my researches I found several postings that NTEXT is deprecated and I should change to NVARCHAR(MAX). But I can't do this without recreating the snapshot, which is quite a problem for the people working with the devices (because of dataamount and time).

Is there a workaround for the insert-trigger to handle the datatypes?

This is where the error is thrown:

DECLARE c1 CURSOR FOR SELECT ControlValue FROM INSERTED
Solution

  • You can do this by reading the data from the underlying table, joining on INSERTED.

    This is the workaround:

    DECLARE c1 CURSOR FOR SELECT t.ControlValue 
from INSERTED i 
  INNER JOIN TableName t on i.PrimaryKey = t.PrimaryKey

    Essentially you need to join the inserted and/or deleted pseudo-tables onto the underlying table and read the NTEXT, TEXT or IMAGE data from the underlying tables.