View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0004915SymmetricDSBugpublic2021-03-24 11:282023-09-13 17:25
Reporterklippit Assigned To 
Prioritynormal 
Status closedResolutionopen 
Summary0004915: SQL Server trigger.update.capture.changed.data.only.enabled=true not working
Descriptiontrigger.update.capture.changed.data.only.enabled=true Property is not working on sql server 2012
Steps To ReproduceI added following line to my file C:\symmetric\engines\server.properties

trigger.update.capture.changed.data.only.enabled=true

After restarting the symmetric service nothing changed in triggers.

Some changes in Database were made.

I executed following query to find out if the new config is working

select count(*) from sym_data where row_data like old_data and create_time>'24.03.2021 11:30:00' (Timestamp is 5 Minutes after Symmetricds restart)

The Query returned more then 6000 rows.

I took a look at the trigger and it always look the same if i change the value form ture to false or otherwise.
Tagsdialect: sql-server, trigger

Activities

klippit

2021-03-24 11:33

reporter   ~0001900

Symmetricds Version 3.10.4

klippit

2021-03-24 11:40

reporter   ~0001901

Trigger output:

USE [postest]
GO
/****** Object: Trigger [dbo].[SYM_ON_U_FOR_MMS_RTCL_SRVR] Script Date: 24.03.2021 11:55:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[SYM_ON_U_FOR_MMS_RTCL_SRVR] on [postest].[dbo].[mms_article] 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 ("postest".dbo.sym_triggers_disabled() = 0) begin
         insert into "postest"."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 'mms_article','U', 95,
          case when inserted."id" is null then '' else ('"' + convert(varchar(40), inserted."id",2) + '"') end+','+
          case when inserted."articlegroup_id" is null then '' else ('"' + convert(varchar(40), inserted."articlegroup_id",2) + '"') end+','+
          case when inserted."number" is null then '' else ('"' + convert(varchar(40), inserted."number",2) + '"') end+','+
          case when inserted."name" is null then '' else '"' + replace(replace(convert(varchar(120),inserted."name") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."print_text" is null then '' else '"' + replace(replace(convert(varchar(120),inserted."print_text") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."descriptionshort" is null then '' else '"' + replace(replace(convert(varchar(1000),inserted."descriptionshort") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."unit_table_id" is null then '' else ('"' + convert(varchar(40), inserted."unit_table_id",2) + '"') end+','+
          case when inserted."base_unit_id" is null then '' else ('"' + convert(varchar(40), inserted."base_unit_id",2) + '"') end+','+
          case when inserted."account_table_id" is null then '' else ('"' + convert(varchar(40), inserted."account_table_id",2) + '"') end+','+
          case when inserted."compositionarticle" is null then '' when inserted."compositionarticle" = 1 then '"1"' else '"0"' end+','+
          case when inserted."lockedforsale" is null then '' when inserted."lockedforsale" = 1 then '"1"' else '"0"' end+','+
          case when inserted."flags" is null then '' else ('"' + convert(varchar(40), inserted."flags",2) + '"') end+','+
          case when inserted."type" is null then '' else ('"' + convert(varchar(40), inserted."type",2) + '"') end+','+
          case when inserted."orderintelligenceclass" is null then '' else '"' + replace(replace(convert(varchar(256),inserted."orderintelligenceclass") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."prepayment_amount" is null then '' else ('"' + convert(varchar(40), inserted."prepayment_amount",2) + '"') end+','+
          case when inserted."showonpos" is null then '' when inserted."showonpos" = 1 then '"1"' else '"0"' end+','+
          case when inserted."prepayment_articlenumber" is null then '' else ('"' + convert(varchar(40), inserted."prepayment_articlenumber",2) + '"') end+','+
          case when inserted."showgroup_id" is null then '' else ('"' + convert(varchar(40), inserted."showgroup_id",2) + '"') end+','+
          case when orig."configuration" is null then '' else '"' + replace(replace(cast(orig."configuration" 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+','+
          case when deleted."articlegroup_id" is null then '' else ('"' + convert(varchar(40), deleted."articlegroup_id",2) + '"') end+','+
          case when deleted."number" is null then '' else ('"' + convert(varchar(40), deleted."number",2) + '"') end+','+
          case when deleted."name" is null then '' else '"' + replace(replace(convert(varchar(120),deleted."name") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."print_text" is null then '' else '"' + replace(replace(convert(varchar(120),deleted."print_text") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."descriptionshort" is null then '' else '"' + replace(replace(convert(varchar(1000),deleted."descriptionshort") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."unit_table_id" is null then '' else ('"' + convert(varchar(40), deleted."unit_table_id",2) + '"') end+','+
          case when deleted."base_unit_id" is null then '' else ('"' + convert(varchar(40), deleted."base_unit_id",2) + '"') end+','+
          case when deleted."account_table_id" is null then '' else ('"' + convert(varchar(40), deleted."account_table_id",2) + '"') end+','+
          case when deleted."compositionarticle" is null then '' when deleted."compositionarticle" = 1 then '"1"' else '"0"' end+','+
          case when deleted."lockedforsale" is null then '' when deleted."lockedforsale" = 1 then '"1"' else '"0"' end+','+
          case when deleted."flags" is null then '' else ('"' + convert(varchar(40), deleted."flags",2) + '"') end+','+
          case when deleted."type" is null then '' else ('"' + convert(varchar(40), deleted."type",2) + '"') end+','+
          case when deleted."orderintelligenceclass" is null then '' else '"' + replace(replace(convert(varchar(256),deleted."orderintelligenceclass") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."prepayment_amount" is null then '' else ('"' + convert(varchar(40), deleted."prepayment_amount",2) + '"') end+','+
          case when deleted."showonpos" is null then '' when deleted."showonpos" = 1 then '"1"' else '"0"' end+','+
          case when deleted."prepayment_articlenumber" is null then '' else ('"' + convert(varchar(40), deleted."prepayment_articlenumber",2) + '"') end+','+
          case when deleted."showgroup_id" is null then '' else ('"' + convert(varchar(40), deleted."showgroup_id",2) + '"') end+','+
          '', 'mms', @TransactionId, "postest".dbo.sym_node_disabled(), null, current_timestamp
                                                                                                                                             
          from (select "id","articlegroup_id","number","name","print_text","descriptionshort","unit_table_id","base_unit_id","account_table_id","compositionarticle","lockedforsale","flags","type","orderintelligenceclass","prepayment_amount","showonpos","prepayment_articlenumber","showgroup_id", row_number() over (order by (select 1)) as __row_num from inserted) inserted inner join "postest"."dbo"."mms_article" orig on orig."id"=inserted."id" inner join (select "id","articlegroup_id","number","name","print_text","descriptionshort","unit_table_id","base_unit_id","account_table_id","compositionarticle","lockedforsale","flags","type","orderintelligenceclass","prepayment_amount","showonpos","prepayment_articlenumber","showgroup_id", row_number() over (order by (select 1)) as __row_num from deleted)deleted on (inserted.__row_num = deleted.__row_num)
                                                                                                                                            
          where 1=1 and (1=1)
       end
                                                                                                                                                    
     if (@NCT = 0) set nocount off
   end
---- go

pmarzullo

2021-03-24 17:59

developer   ~0001902

Can you run the following command and check if the trigger then starts to capture only changed data?
symadmin -f sync-triggers

klippit

2021-03-25 06:35

reporter   ~0001903

Trigger changed to:

USE [postest]
GO
/****** Object: Trigger [dbo].[SYM_ON_U_FOR_MMS_RTCL_SRVR] Script Date: 25.03.2021 07:29:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[SYM_ON_U_FOR_MMS_RTCL_SRVR] on [postest].[dbo].[mms_article] 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 ("postest".dbo.sym_triggers_disabled() = 0) begin
         insert into "postest"."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 'mms_article','U', 209,
          case when inserted."id" is null then '' else ('"' + convert(varchar(40), inserted."id",2) + '"') end+','+
          case when inserted."articlegroup_id" is null then '' else ('"' + convert(varchar(40), inserted."articlegroup_id",2) + '"') end+','+
          case when inserted."number" is null then '' else ('"' + convert(varchar(40), inserted."number",2) + '"') end+','+
          case when inserted."name" is null then '' else '"' + replace(replace(convert(varchar(120),inserted."name") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."print_text" is null then '' else '"' + replace(replace(convert(varchar(120),inserted."print_text") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."descriptionshort" is null then '' else '"' + replace(replace(convert(varchar(1000),inserted."descriptionshort") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."unit_table_id" is null then '' else ('"' + convert(varchar(40), inserted."unit_table_id",2) + '"') end+','+
          case when inserted."base_unit_id" is null then '' else ('"' + convert(varchar(40), inserted."base_unit_id",2) + '"') end+','+
          case when inserted."account_table_id" is null then '' else ('"' + convert(varchar(40), inserted."account_table_id",2) + '"') end+','+
          case when inserted."compositionarticle" is null then '' when inserted."compositionarticle" = 1 then '"1"' else '"0"' end+','+
          case when inserted."lockedforsale" is null then '' when inserted."lockedforsale" = 1 then '"1"' else '"0"' end+','+
          case when inserted."flags" is null then '' else ('"' + convert(varchar(40), inserted."flags",2) + '"') end+','+
          case when inserted."type" is null then '' else ('"' + convert(varchar(40), inserted."type",2) + '"') end+','+
          case when inserted."orderintelligenceclass" is null then '' else '"' + replace(replace(convert(varchar(256),inserted."orderintelligenceclass") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."prepayment_amount" is null then '' else ('"' + convert(varchar(40), inserted."prepayment_amount",2) + '"') end+','+
          case when inserted."showonpos" is null then '' when inserted."showonpos" = 1 then '"1"' else '"0"' end+','+
          case when inserted."prepayment_articlenumber" is null then '' else ('"' + convert(varchar(40), inserted."prepayment_articlenumber",2) + '"') end+','+
          case when inserted."showgroup_id" is null then '' else ('"' + convert(varchar(40), inserted."showgroup_id",2) + '"') end+','+
          case when orig."configuration" is null then '' else '"' + replace(replace(cast(orig."configuration" 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+','+
          case when deleted."articlegroup_id" is null then '' else ('"' + convert(varchar(40), deleted."articlegroup_id",2) + '"') end+','+
          case when deleted."number" is null then '' else ('"' + convert(varchar(40), deleted."number",2) + '"') end+','+
          case when deleted."name" is null then '' else '"' + replace(replace(convert(varchar(120),deleted."name") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."print_text" is null then '' else '"' + replace(replace(convert(varchar(120),deleted."print_text") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."descriptionshort" is null then '' else '"' + replace(replace(convert(varchar(1000),deleted."descriptionshort") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."unit_table_id" is null then '' else ('"' + convert(varchar(40), deleted."unit_table_id",2) + '"') end+','+
          case when deleted."base_unit_id" is null then '' else ('"' + convert(varchar(40), deleted."base_unit_id",2) + '"') end+','+
          case when deleted."account_table_id" is null then '' else ('"' + convert(varchar(40), deleted."account_table_id",2) + '"') end+','+
          case when deleted."compositionarticle" is null then '' when deleted."compositionarticle" = 1 then '"1"' else '"0"' end+','+
          case when deleted."lockedforsale" is null then '' when deleted."lockedforsale" = 1 then '"1"' else '"0"' end+','+
          case when deleted."flags" is null then '' else ('"' + convert(varchar(40), deleted."flags",2) + '"') end+','+
          case when deleted."type" is null then '' else ('"' + convert(varchar(40), deleted."type",2) + '"') end+','+
          case when deleted."orderintelligenceclass" is null then '' else '"' + replace(replace(convert(varchar(256),deleted."orderintelligenceclass") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."prepayment_amount" is null then '' else ('"' + convert(varchar(40), deleted."prepayment_amount",2) + '"') end+','+
          case when deleted."showonpos" is null then '' when deleted."showonpos" = 1 then '"1"' else '"0"' end+','+
          case when deleted."prepayment_articlenumber" is null then '' else ('"' + convert(varchar(40), deleted."prepayment_articlenumber",2) + '"') end+','+
          case when deleted."showgroup_id" is null then '' else ('"' + convert(varchar(40), deleted."showgroup_id",2) + '"') end+','+
          '', 'mms', @TransactionId, "postest".dbo.sym_node_disabled(), null, current_timestamp
                                                                                                                                             
          from (select "id","articlegroup_id","number","name","print_text","descriptionshort","unit_table_id","base_unit_id","account_table_id","compositionarticle","lockedforsale","flags","type","orderintelligenceclass","prepayment_amount","showonpos","prepayment_articlenumber","showgroup_id", row_number() over (order by (select 1)) as __row_num from inserted) inserted inner join "postest"."dbo"."mms_article" orig on orig."id"=inserted."id" inner join (select "id","articlegroup_id","number","name","print_text","descriptionshort","unit_table_id","base_unit_id","account_table_id","compositionarticle","lockedforsale","flags","type","orderintelligenceclass","prepayment_amount","showonpos","prepayment_articlenumber","showgroup_id", row_number() over (order by (select 1)) as __row_num from deleted)deleted on (inserted.__row_num = deleted.__row_num)
                                                                                                                                            
          where 1=1 and (((inserted."id" IS NOT NULL AND deleted."id" IS NOT NULL AND inserted."id"<>deleted."id") or (inserted."id" IS NULL AND deleted."id" IS NOT NULL) or (inserted."id" IS NOT NULL AND deleted."id" IS NULL)) or ((inserted."articlegroup_id" IS NOT NULL AND deleted."articlegroup_id" IS NOT NULL AND inserted."articlegroup_id"<>deleted."articlegroup_id") or (inserted."articlegroup_id" IS NULL AND deleted."articlegroup_id" IS NOT NULL) or (inserted."articlegroup_id" IS NOT NULL AND deleted."articlegroup_id" IS NULL)) or ((inserted."number" IS NOT NULL AND deleted."number" IS NOT NULL AND inserted."number"<>deleted."number") or (inserted."number" IS NULL AND deleted."number" IS NOT NULL) or (inserted."number" IS NOT NULL AND deleted."number" IS NULL)) or ((inserted."name" IS NOT NULL AND deleted."name" IS NOT NULL AND inserted."name"<>deleted."name") or (inserted."name" IS NULL AND deleted."name" IS NOT NULL) or (inserted."name" IS NOT NULL AND deleted."name" IS NULL)) or ((inserted."print_text" IS NOT NULL AND deleted."print_text" IS NOT NULL AND inserted."print_text"<>deleted."print_text") or (inserted."print_text" IS NULL AND deleted."print_text" IS NOT NULL) or (inserted."print_text" IS NOT NULL AND deleted."print_text" IS NULL)) or ((inserted."descriptionshort" IS NOT NULL AND deleted."descriptionshort" IS NOT NULL AND inserted."descriptionshort"<>deleted."descriptionshort") or (inserted."descriptionshort" IS NULL AND deleted."descriptionshort" IS NOT NULL) or (inserted."descriptionshort" IS NOT NULL AND deleted."descriptionshort" IS NULL)) or ((inserted."unit_table_id" IS NOT NULL AND deleted."unit_table_id" IS NOT NULL AND inserted."unit_table_id"<>deleted."unit_table_id") or (inserted."unit_table_id" IS NULL AND deleted."unit_table_id" IS NOT NULL) or (inserted."unit_table_id" IS NOT NULL AND deleted."unit_table_id" IS NULL)) or ((inserted."base_unit_id" IS NOT NULL AND deleted."base_unit_id" IS NOT NULL AND inserted."base_unit_id"<>deleted."base_unit_id") or (inserted."base_unit_id" IS NULL AND deleted."base_unit_id" IS NOT NULL) or (inserted."base_unit_id" IS NOT NULL AND deleted."base_unit_id" IS NULL)) or ((inserted."account_table_id" IS NOT NULL AND deleted."account_table_id" IS NOT NULL AND inserted."account_table_id"<>deleted."account_table_id") or (inserted."account_table_id" IS NULL AND deleted."account_table_id" IS NOT NULL) or (inserted."account_table_id" IS NOT NULL AND deleted."account_table_id" IS NULL)) or ((inserted."compositionarticle" IS NOT NULL AND deleted."compositionarticle" IS NOT NULL AND inserted."compositionarticle"<>deleted."compositionarticle") or (inserted."compositionarticle" IS NULL AND deleted."compositionarticle" IS NOT NULL) or (inserted."compositionarticle" IS NOT NULL AND deleted."compositionarticle" IS NULL)) or ((inserted."lockedforsale" IS NOT NULL AND deleted."lockedforsale" IS NOT NULL AND inserted."lockedforsale"<>deleted."lockedforsale") or (inserted."lockedforsale" IS NULL AND deleted."lockedforsale" IS NOT NULL) or (inserted."lockedforsale" IS NOT NULL AND deleted."lockedforsale" IS NULL)) or ((inserted."flags" IS NOT NULL AND deleted."flags" IS NOT NULL AND inserted."flags"<>deleted."flags") or (inserted."flags" IS NULL AND deleted."flags" IS NOT NULL) or (inserted."flags" IS NOT NULL AND deleted."flags" IS NULL)) or ((inserted."type" IS NOT NULL AND deleted."type" IS NOT NULL AND inserted."type"<>deleted."type") or (inserted."type" IS NULL AND deleted."type" IS NOT NULL) or (inserted."type" IS NOT NULL AND deleted."type" IS NULL)) or ((inserted."orderintelligenceclass" IS NOT NULL AND deleted."orderintelligenceclass" IS NOT NULL AND inserted."orderintelligenceclass"<>deleted."orderintelligenceclass") or (inserted."orderintelligenceclass" IS NULL AND deleted."orderintelligenceclass" IS NOT NULL) or (inserted."orderintelligenceclass" IS NOT NULL AND deleted."orderintelligenceclass" IS NULL)) or ((inserted."prepayment_amount" IS NOT NULL AND deleted."prepayment_amount" IS NOT NULL AND inserted."prepayment_amount"<>deleted."prepayment_amount") or (inserted."prepayment_amount" IS NULL AND deleted."prepayment_amount" IS NOT NULL) or (inserted."prepayment_amount" IS NOT NULL AND deleted."prepayment_amount" IS NULL)) or ((inserted."showonpos" IS NOT NULL AND deleted."showonpos" IS NOT NULL AND inserted."showonpos"<>deleted."showonpos") or (inserted."showonpos" IS NULL AND deleted."showonpos" IS NOT NULL) or (inserted."showonpos" IS NOT NULL AND deleted."showonpos" IS NULL)) or ((inserted."prepayment_articlenumber" IS NOT NULL AND deleted."prepayment_articlenumber" IS NOT NULL AND inserted."prepayment_articlenumber"<>deleted."prepayment_articlenumber") or (inserted."prepayment_articlenumber" IS NULL AND deleted."prepayment_articlenumber" IS NOT NULL) or (inserted."prepayment_articlenumber" IS NOT NULL AND deleted."prepayment_articlenumber" IS NULL)) or ((inserted."showgroup_id" IS NOT NULL AND deleted."showgroup_id" IS NOT NULL AND inserted."showgroup_id"<>deleted."showgroup_id") or (inserted."showgroup_id" IS NULL AND deleted."showgroup_id" IS NOT NULL) or (inserted."showgroup_id" IS NOT NULL AND deleted."showgroup_id" IS NULL)) or UPDATE("configuration"))
       end
                                                                                                                                                    
     if (@NCT = 0) set nocount off
   end
---- go


select old_data,row_data from sym_data where row_data like old_data and create_time>'25.03.2021 07:10'

Query returns now 210 rows.

2 Example Rows are:
old_data: "2812497967293008678","2812497967292881055","1","350.0000", row_data: "2812497967293008678","2812497967292881055","1","350.0000",
old_data: "2812497967293008679","2812497967292881056","991","350.0000", row_data: "2812497967293008679","2812497967292881056","991","350.0000",

Trigger for this table is:

USE [postest]
GO
/****** Object: Trigger [dbo].[SYM_ON_U_FOR_PS_TRMNL_SRVR] Script Date: 25.03.2021 07:35:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[SYM_ON_U_FOR_PS_TRMNL_SRVR] on [postest].[dbo].[pos_terminal] 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 ("postest".dbo.sym_triggers_disabled() = 0) begin
         insert into "postest"."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 'pos_terminal','U', 220,
          case when inserted."id" is null then '' else ('"' + convert(varchar(40), inserted."id",2) + '"') end+','+
          case when inserted."shop_id" is null then '' else ('"' + convert(varchar(40), inserted."shop_id",2) + '"') end+','+
          case when inserted."pos_id" is null then '' else '"' + replace(replace(convert(varchar(15),inserted."pos_id") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."change_amount" is null then '' else ('"' + convert(varchar(40), inserted."change_amount",2) + '"') end+','+
          case when orig."configuration" is null then '' else '"' + replace(replace(cast(orig."configuration" 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+','+
          case when deleted."shop_id" is null then '' else ('"' + convert(varchar(40), deleted."shop_id",2) + '"') end+','+
          case when deleted."pos_id" is null then '' else '"' + replace(replace(convert(varchar(15),deleted."pos_id") ,'\','\\'),'"','\"') + '"' end+','+
          case when deleted."change_amount" is null then '' else ('"' + convert(varchar(40), deleted."change_amount",2) + '"') end+','+
          '', 'pos', @TransactionId, "postest".dbo.sym_node_disabled(), (select inserted.shop_id), current_timestamp
                                                                                                                                             
          from (select "id","shop_id","pos_id","change_amount", row_number() over (order by (select 1)) as __row_num from inserted) inserted inner join "postest"."dbo"."pos_terminal" orig on orig."id"=inserted."id" inner join (select "id","shop_id","pos_id","change_amount", row_number() over (order by (select 1)) as __row_num from deleted)deleted on (inserted.__row_num = deleted.__row_num)
                                                                                                                                            
          where 1=1 and (((inserted."id" IS NOT NULL AND deleted."id" IS NOT NULL AND inserted."id"<>deleted."id") or (inserted."id" IS NULL AND deleted."id" IS NOT NULL) or (inserted."id" IS NOT NULL AND deleted."id" IS NULL)) or ((inserted."shop_id" IS NOT NULL AND deleted."shop_id" IS NOT NULL AND inserted."shop_id"<>deleted."shop_id") or (inserted."shop_id" IS NULL AND deleted."shop_id" IS NOT NULL) or (inserted."shop_id" IS NOT NULL AND deleted."shop_id" IS NULL)) or ((inserted."pos_id" IS NOT NULL AND deleted."pos_id" IS NOT NULL AND inserted."pos_id"<>deleted."pos_id") or (inserted."pos_id" IS NULL AND deleted."pos_id" IS NOT NULL) or (inserted."pos_id" IS NOT NULL AND deleted."pos_id" IS NULL)) or ((inserted."change_amount" IS NOT NULL AND deleted."change_amount" IS NOT NULL AND inserted."change_amount"<>deleted."change_amount") or (inserted."change_amount" IS NULL AND deleted."change_amount" IS NOT NULL) or (inserted."change_amount" IS NOT NULL AND deleted."change_amount" IS NULL)) or UPDATE("configuration"))
       end
                                                                                                                                                    
     if (@NCT = 0) set nocount off
   end
---- go

klippit

2021-03-25 06:56

reporter   ~0001904

i found out why the query returns rows from pos_terminal
there are some excluded columns but trigger for changes checks if these colums have changed but should not check excluded columns

klippit

2021-03-25 10:56

reporter   ~0001905

did some testing and now it works for every table, but not for mms_article

There are again entries where row_data and old_data are equal

old_data "2812497967292909514","2812497967292906200","10020313","Special Color Conditioner",,"Erfrischender Farbschutz-Conditioner",,"2812497967292906935","2812497967292906874","0","1","0","1",,,"0","0",,
row_data "2812497967292909514","2812497967292906200","10020313","Special Color Conditioner",,"Erfrischender Farbschutz-Conditioner",,"2812497967292906935","2812497967292906874","0","1","0","1",,,"0","0",,

i also created a hash column which is equal
query is:
select old_data,row_data,checksum(CAST(old_data as varchar)),checksum(CAST(row_data as varchar)) from sym_data where row_data like old_data and create_time>'25.03.2021 11:38'

klippit

2021-04-14 06:10

reporter   ~0001912

mms_article is now working.
other tables still dont work
if a table has a column with type text this column is missing in trigger check for changed data

elong

2021-04-14 18:43

developer   ~0001916

We can't compare image, text, and ntext because SQL-Server doesn't allow those types in a comparison.

elong

2021-04-14 18:54

developer   ~0001917

Are you using a image/text/ntext in the update statement's SET clause? It will assume that you are setting it, so it is changing, since it can't compare the old and new for that data type. If you remove the image/text/ntext columns from the update statement's SET clause, then it will correctly detect if a change is happening or not.

Issue History

Date Modified Username Field Change
2021-03-24 11:28 klippit New Issue
2021-03-24 11:33 klippit Note Added: 0001900
2021-03-24 11:40 klippit Note Added: 0001901
2021-03-24 17:59 pmarzullo Note Added: 0001902
2021-03-25 06:35 klippit Note Added: 0001903
2021-03-25 06:56 klippit Note Added: 0001904
2021-03-25 10:56 klippit Note Added: 0001905
2021-04-14 06:10 klippit Note Added: 0001912
2021-04-14 18:43 elong Note Added: 0001916
2021-04-14 18:54 elong Note Added: 0001917
2022-08-02 20:17 elong Tag Attached: trigger
2022-08-02 20:17 elong Tag Attached: dialect: sql-server
2022-08-18 20:39 elong Status new => feedback
2023-09-13 17:25 emiller Status feedback => closed