View Issue Details

IDProjectCategoryView StatusLast Update
0002597SymmetricDSBugpublic2019-05-31 10:41
ReportertbergerAssigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.7.32 
Target VersionFixed in Version 
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):

    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).
Tagsconflict manager, dialect: sql-server, trigger

Activities

elong

2019-05-31 10: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;
                    }
                    break;

Need to research why it's written this way.

Issue History

Date Modified Username Field Change
2016-05-13 12:14 tberger New Issue
2019-04-24 08:49 elong Tag Attached: trigger
2019-04-24 08:49 elong Tag Attached: dialect: mssql
2019-04-24 08:50 admin Tag Renamed dialect: mssql => dialect: sql-server
2019-05-31 10:41 elong Note Added: 0001515
2019-05-31 10:41 elong Tag Attached: conflict manager