View Issue Details

IDProjectCategoryView StatusLast Update
0002597SymmetricDSBugpublic2022-08-11 18:20
Reportertberger Assigned To 
Status newResolutionopen 
Product Version3.7.32 
Summary0002597: Bug in update trigger for table with nvarchar(MAX) field (MS SQL)
DescriptionThis is the crucial part of the update-trigger text for a simple test table consisting just of an id column (int) and a text column (nvarchar(MAX)) (using MS SQL Server 2008 here):

          case when inserted."id" is null then '' else ('"' + convert(varchar(40), inserted."id",2) + '"') end+','+
          case when orig."text" is null then '' else '"' + replace(replace(cast(orig."text" as varchar(max)),'\','\\'),'"','\"') + '"' end,
          case when deleted."id" is null then '' else ('"' + convert(varchar(40), deleted."id",2) + '"') end,
          case when deleted."id" is null then '' else ('"' + convert(varchar(40), deleted."id",2) + '"') end+','+
          '' ,deleted."id" ,inserted."id", 'testchannel' from inserted inner join "dbo"."testtable" orig on orig."id"=inserted."id" inner join deleted on deleted."id"=inserted."id" where 1=1

    open DataCursor
          fetch next from DataCursor into @DataRow, @OldPk, @OldDataRow ,@oldpk0 ,@newpk0, @ChannelId

Note how @OldDataRow unconditionally gets an empty value for the text column (and curiously, for the current value `orig` instead of `inserted` is used). This obviously creates a problem if a conflict detection type of USE_OLD_DATA or USE_CHANGED_DATA is used as a conflict will be always detected (unless the column value was NULL before).
Tagsconflict manager, dialect: sql-server, trigger


related to 0000262 closedchenson Blob/Clob not updating for MySQL and Derby 



2019-05-31 14:41

developer   ~0001515

The trigger text for an update that leaves out the old column value for a CLOB appears to be intentional. In AbstractTriggerTemplate.fillOutColumnTemplate():

                case Types.CLOB:
                    if (isOld && symmetricDialect.needsToSelectLobData()) {
                        templateToUse = emptyColumnTemplate;
                    } else {
                        templateToUse = clobColumnTemplate;

Need to research why it's written this way.


2022-08-11 14:44

developer   ~0002156

The code mentioned in the above note was added back in 2010 to fix issue 0000262. This issue applied to CLOB and LOB columns because it is not possible to access the old value within the update trigger, but the code change also affects nvarchar(max) columns.

It looks like it is possible to access the old value from an nvarchar(max) column within an update trigger. I expect the same to apply to varbinary(max) columns. We could implement proper capture with those types, but we would likely have to implement a new column template specific to SQL Server.


2022-08-11 18:20

developer   ~0002158

Agreed with @emiller. A trigger on SQL-Server is not allowed to access LOB columns in the "insert" or "deleted" virtual views. That is why a LOB column is referenced in "orig" alias for the table being captured -- it joins to the table on the primary to try and get the current value. So, nvarchar(max) is currently being treated like it is a LOB data type, and that works, but it can't capture the old value with that technique.

Issue History

Date Modified Username Field Change
2016-05-13 16:14 tberger New Issue
2019-04-24 12:49 elong Tag Attached: trigger
2019-04-24 12:49 elong Tag Attached: dialect: mssql
2019-04-24 12:50 admin Tag Renamed dialect: mssql => dialect: sql-server
2019-05-31 14:41 elong Note Added: 0001515
2019-05-31 14:41 elong Tag Attached: conflict manager
2022-08-11 13:26 emiller Relationship added related to 0000262
2022-08-11 14:44 emiller Note Added: 0002156
2022-08-11 18:20 elong Note Added: 0002158