View Issue Details

IDProjectCategoryView StatusLast Update
0003899SymmetricDSBugpublic2019-04-23 11:14
ReportermmichalekAssigned Topmarzullo 
Priorityhigh 
Status closedResolutionfixed 
Product Version3.9.0 
Target Version3.9.19Fixed in Version3.9.19 
Summary0003899: When changing just a LOB field on SQL Server, the change is not captured by the trigger
DescriptionThis only applies when trigger.update.capture.changed.data.only.enabled=true

The issue is that the trigger template does not include the LOB (e.g. TEXT) field for purposes of comparison, and discards the change if non of the other non-lob fields have changed. Consider converting the where clause to an IF instead and using the UPDATE(column) function on all the columns (including lobs) instead.

This sample trigger will not capture a sym_paramter change if on the param_value is changed.

create trigger SYM_ON_U_FOR_SYM_PRMTR_XCNTR on "rws_xcenter_dev"."dbo"."sym_parameter" with execute as caller after update as
   begin
     declare @NCT int
     set @NCT = @@OPTIONS & 512
     set nocount on
     declare @TransactionId varchar(1000)
     if (@@TRANCOUNT > 0) begin
       select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0
     end
      
     if ("rws_xcenter_dev".dbo.sym_triggers_disabled() = 0) begin
         insert into "rws_xcenter_dev"."dbo".sym_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)
             select 'sym_parameter','U', 5758,
          case when inserted."external_id" is null then '' else '"' + replace(replace(convert(varchar(255),inserted."external_id") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."node_group_id" is null then '' else '"' + replace(replace(convert(varchar(50),inserted."node_group_id") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."param_key" is null then '' else '"' + replace(replace(convert(varchar(80),inserted."param_key") ,'\','\\'),'"','\"') + '"' end+','+
          case when orig."param_value" is null then '' else '"' + replace(replace(cast(orig."param_value" as varchar(max)),'\','\\'),'"','\"') + '"' end+','+
          case when inserted."create_time" is null then '' else ('"' + convert(varchar,inserted."create_time",121) + '"') end+','+
          case when inserted."last_update_by" is null then '' else '"' + replace(replace(convert(varchar(50),inserted."last_update_by") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."last_update_time" is null then '' else ('"' + convert(varchar,inserted."last_update_time",121) + '"') end,
          case when deleted."external_id" is null then '' else '"' + replace(replace(convert(varchar(255),deleted."external_id") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."node_group_id" is null then '' else '"' + replace(replace(convert(varchar(50),deleted."node_group_id") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."param_key" is null then '' else '"' + replace(replace(convert(varchar(80),deleted."param_key") ,'\','\\'),'"','\"') + '"' end, null, 'config', @TransactionId, "rws_xcenter_dev".dbo.sym_node_disabled(), null, current_timestamp
                                                                                                                                             
          from inserted inner join "rws_xcenter_dev"."dbo"."sym_parameter" orig on orig."external_id"=inserted."external_id" and orig."node_group_id"=inserted."node_group_id" and orig."param_key"=inserted."param_key" inner join deleted on deleted."external_id"=inserted."external_id" and deleted."node_group_id"=inserted."node_group_id" and deleted."param_key"=inserted."param_key"
                                                                                                                                            
          where 1=1 and (((UPDATE(param_value) OR (inserted."external_id" IS NOT NULL AND deleted."external_id" IS NOT NULL AND inserted."external_id"<>deleted."external_id") or (inserted."external_id" IS NULL AND deleted."external_id" IS NOT NULL) or (inserted."external_id" IS NOT NULL AND deleted."external_id" IS NULL)) or ((inserted."node_group_id" IS NOT NULL AND deleted."node_group_id" IS NOT NULL AND inserted."node_group_id"<>deleted."node_group_id") or (inserted."node_group_id" IS NULL AND deleted."node_group_id" IS NOT NULL) or (inserted."node_group_id" IS NOT NULL AND deleted."node_group_id" IS NULL)) or ((inserted."param_key" IS NOT NULL AND deleted."param_key" IS NOT NULL AND inserted."param_key"<>deleted."param_key") or (inserted."param_key" IS NULL AND deleted."param_key" IS NOT NULL) or (inserted."param_key" IS NOT NULL AND deleted."param_key" IS NULL)) or ((inserted."create_time" IS NOT NULL AND deleted."create_time" IS NOT NULL AND inserted."create_time"<>deleted."create_time") or (inserted."create_time" IS NULL AND deleted."create_time" IS NOT NULL) or (inserted."create_time" IS NOT NULL AND deleted."create_time" IS NULL)) or ((inserted."last_update_by" IS NOT NULL AND deleted."last_update_by" IS NOT NULL AND inserted."last_update_by"<>deleted."last_update_by") or (inserted."last_update_by" IS NULL AND deleted."last_update_by" IS NOT NULL) or (inserted."last_update_by" IS NOT NULL AND deleted."last_update_by" IS NULL)) or ((inserted."last_update_time" IS NOT NULL AND deleted."last_update_time" IS NOT NULL AND inserted."last_update_time"<>deleted."last_update_time") or (inserted."last_update_time" IS NULL AND deleted."last_update_time" IS NOT NULL) or (inserted."last_update_time" IS NOT NULL AND deleted."last_update_time" IS NULL)))
       end
                                                                                                                                                    
     if (@NCT = 0) set nocount off
   end
---- go
TagsNo tags attached.

Activities

pmarzullo

2019-04-05 11:26

developer   ~0001402

Because the UPDATE() function does not compare the values, and only indicates whether the column was in the SET list, leave the non-LOB columns as is (comparing null/non-null and comparing the values when both non-null) and just add the LOB columns (TEXT, NTEXT, IMAGE) in the list of columns to check as "UPDATE(lob-column)".

Related Changesets

SymmetricDS: 3.9 6aba9f6b

2019-04-05 11:35:10

Philip Marzullo

Details Diff
0003899: When changing just a LOB field on SQL Server, the change is not
captured by the trigger

0003899
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/JdbcSymmetricDialectFactory.java Diff File
add - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSql2008SymmetricDialect.java Diff File
add - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSql2008TriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java Diff File

SymmetricDS: 3.10 e038fdc8

2019-04-05 11:51:52

Philip Marzullo

Details Diff
0003899: When changing just a LOB field on SQL Server, the change is not
captured by the trigger

0003899
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/JdbcSymmetricDialectFactory.java Diff File
add - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSql2008SymmetricDialect.java Diff File
add - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSql2008TriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java Diff File

Issue History

Date Modified Username Field Change
2019-04-04 12:46 mmichalek New Issue
2019-04-05 11:26 pmarzullo Note Added: 0001402
2019-04-05 11:27 pmarzullo Assigned To => pmarzullo
2019-04-05 11:27 pmarzullo Status new => assigned
2019-04-05 11:53 pmarzullo Fixed in Version => 3.9.19
2019-04-05 11:59 pmarzullo Status assigned => resolved
2019-04-05 11:59 pmarzullo Resolution open => fixed
2019-04-05 12:00 Changeset attached => SymmetricDS 3.9 6aba9f6b
2019-04-05 12:00 Changeset attached => SymmetricDS 3.10 e038fdc8
2019-04-23 11:14 elong Status resolved => closed