View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0004317SymmetricDSBugpublic2020-03-16 12:522020-03-16 12:52
Reporterfcardara Assigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.11.6 
Summary0004317: Mssql - On table with VARCHAR (MAX) column Insert trigget fails
DescriptionOn table with VARCHAR (MAX) column when I try to insert null values, the trigger daosn't inset any rows in sys_data.

The table doesn't have a primary key.

Below the generated trigger:

USE [gwsv_pirinat]
GO
/****** Object: Trigger [dbo].[SYM_ON_I_FOR_GR_GRFS_DRW_RSTS_CLNTS] Script Date: 16/03/2020 12:53:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[SYM_ON_I_FOR_GR_GRFS_DRW_RSTS_CLNTS] on [dbo].[GR_GRAFOS_DRW_ARISTAS] with execute as caller after insert 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 ("gwsv_pirinat".dbo.sym_triggers_disabled() = 0) begin
         insert into "gwsv_pirinat"."symds".sym_data
           (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)
          select 'gr_grafos_drw_aristas','I', 38,
          case when inserted."UUID" is null then '' else '"' + replace(replace(convert(varchar(36),inserted."UUID") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."USU_id" is null then '' else '"' + replace(replace(convert(varchar(36),inserted."USU_id") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."GR_Id_grafico" is null then '' else '"' + replace(replace(convert(varchar(36),inserted."GR_Id_grafico") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."GR_Id_node_ori" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."GR_Id_node_ori") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."GR_Id_node_des" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."GR_Id_node_des") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."GR_factor_edge1" is null then '' else '"' + replace(replace(convert(varchar(6),inserted."GR_factor_edge1") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."GR_factor_edge2" is null then '' else '"' + replace(replace(convert(varchar(6),inserted."GR_factor_edge2") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."GR_factor_edge3" is null then '' else '"' + replace(replace(convert(varchar(6),inserted."GR_factor_edge3") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."GR_factor_edge4" is null then '' else '"' + replace(replace(convert(varchar(6),inserted."GR_factor_edge4") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."GR_factor_edge5" is null then '' else '"' + replace(replace(convert(varchar(6),inserted."GR_factor_edge5") ,'\','\\'),'"','\"') + '"' end+','+
          case when orig."GR_label_edge" is null then '' else '"' + replace(replace(cast(orig."GR_label_edge" as varchar(max)),'\','\\'),'"','\"') + '"' end,
                  'traceability', @TransactionId, "gwsv_pirinat".dbo.sym_node_disabled(), null, current_timestamp
                                                                                                                                             
          from inserted inner join "dbo"."GR_GRAFOS_DRW_ARISTAS" orig on orig."UUID"=inserted."UUID" and orig."USU_id"=inserted."USU_id" and orig."GR_Id_grafico"=inserted."GR_Id_grafico" and orig."GR_Id_node_ori"=inserted."GR_Id_node_ori" and orig."GR_Id_node_des"=inserted."GR_Id_node_des" and orig."GR_factor_edge1"=inserted."GR_factor_edge1" and orig."GR_factor_edge2"=inserted."GR_factor_edge2" and orig."GR_factor_edge3"=inserted."GR_factor_edge3" and orig."GR_factor_edge4"=inserted."GR_factor_edge4" and orig."GR_factor_edge5"=inserted."GR_factor_edge5"
                                                                                                                                            
          where 1=1
     end
                                                                                                                                                  
     if (@NCT = 0) set nocount off



Thanks!
TagsNo tags attached.

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2020-03-16 12:52 fcardara New Issue