View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002597 | SymmetricDS | Bug | public | 2016-05-13 16:14 | 2025-03-26 17:17 |
| Reporter | tberger | Assigned To | |||
| Priority | normal | ||||
| Status | acknowledged | Resolution | open | ||
| Product Version | 3.7.32 | ||||
| Summary | 0002597: Bug in update trigger for table with nvarchar(MAX) field (MS SQL) | ||||
| Description | This 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): select 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). | ||||
| Tags | conflict manager, dialect: sql-server, trigger | ||||
|
|
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; } break; Need to research why it's written this way. |
|
|
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. |
|
|
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. |
|
|
This might have been fixed in issue 0006621. We need to re-test this. |
| 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 | |
| 2025-03-26 17:16 | emiller | Relationship added | related to 0006621 |
| 2025-03-26 17:16 | emiller | Note Added: 0002741 | |
| 2025-03-26 17:17 | emiller | Status | new => acknowledged |