View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003902SymmetricDSBugpublic2019-04-05 15:572019-04-23 15:41
Reporterpmarzullo Assigned Topmarzullo  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.9.0 
Target Version3.10.1Fixed in Version3.10.1 
Summary0003902: When changing just a LOB field on SQL Server, the change is not captured by the trigger (fixed on 0003899 in version 3.9.19)
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
Additional InformationBecause 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)".
TagsNo tags attached.

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2019-04-05 15:57 pmarzullo New Issue
2019-04-05 15:57 pmarzullo Status new => assigned
2019-04-05 15:57 pmarzullo Assigned To => pmarzullo
2019-04-05 15:58 pmarzullo Fixed in Version => 3.10.1
2019-04-05 15:59 pmarzullo Status assigned => resolved
2019-04-05 15:59 pmarzullo Resolution open => fixed
2019-04-23 15:41 elong Status resolved => closed