View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003909 | SymmetricDS | Bug | public | 2019-04-09 16:18 | 2019-04-23 15:12 |
| Reporter | pmarzullo | Assigned To | pmarzullo | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.8.42 | ||||
| Target Version | 3.8.43 | Fixed in Version | 3.8.43 | ||
| Summary | 0003909: 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) | ||||
| Description | his 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 Information | 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)". | ||||
| Tags | No tags attached. | ||||
|
SymmetricDS: 3.8 337a4efa 2019-04-09 12:19:03 Philip Marzullo Details Diff |
0003909: 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) |
Affected Issues 0003909 |
|
| 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 | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2019-04-09 16:18 | pmarzullo | New Issue | |
| 2019-04-09 16:18 | pmarzullo | Status | new => assigned |
| 2019-04-09 16:18 | pmarzullo | Assigned To | => pmarzullo |
| 2019-04-09 16:20 | pmarzullo | Status | assigned => resolved |
| 2019-04-09 16:20 | pmarzullo | Resolution | open => fixed |
| 2019-04-09 16:20 | pmarzullo | Fixed in Version | => 3.8.43 |
| 2019-04-09 17:00 | Changeset attached | => SymmetricDS 3.8 337a4efa | |
| 2019-04-23 15:12 | elong | Status | resolved => closed |