View Issue Details

IDProjectCategoryView StatusLast Update
0004201SymmetricDSBugpublic2020-05-22 14:13
ReportermkurzAssigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.11.2 
Target Version3.11.10Fixed in Version 
Summary0004201: Failed to create update trigger for triggers that use and external_select with $(curTriggerValue)
DescriptionWe observe several
"Failed to create trigger x ... Reason The multi-part identifier "inserted.customerid" could not be bound"
for all update triggers that use and external_select with $(curTriggerValue) on a MsSql Server. See central_3.11_log_snippet.log.

First analyze shows that happens after
https://www.symmetricds.org/issues/view.php?id=3901
MsSqlTriggerTemplate.java -> updateTriggerTemplate. I guess it affects updateReloadTriggerTemplate, too

I formatted the sql and made it work. Diff the enclosed failing_sql.txt with fixed_sql.txt, please.
TagsNo tags attached.

Activities

mkurz

2019-12-12 05:31

reporter  

central_3.11_log_snippet.log (26,966 bytes)
failing_sql.txt (15,591 bytes)
CREATE TRIGGER sym_on_u_for_gr_cstmrpctr_c2m_cntrl ON "dbo"."gr_CustomerPicture" 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 (dbo.sym_triggers_disabled() = 0) 
BEGIN 
  IF (1=1 
  AND 
  ( 
         UPDATE(sym_row_id) ) ) 
  BEGIN 
    INSERT INTO "grips"."dbo".sym_data 
                ( 
                            table_name, 
                            event_type, 
                            trigger_hist_id, 
                            pk_data, 
                            channel_id, 
                            transaction_id, 
                            source_node_id, 
                            external_data, 
                            create_time 
                ) 
    SELECT 'GR_CUSTOMERPICTURE', 
           'D', 
           34, 
           CASE 
                  WHEN deleted."sym_row_id" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(36),deleted."sym_row_id") ,'\','\\'),'"','\"') + '"'
           END, 
           'content', 
           @TransactionId, 
           "grips".dbo.sym_node_disabled(), 
           (inserted.customerid), 
           CURRENT_TIMESTAMP 
    FROM   deleted 
    WHERE  EXISTS 
           ( 
                  SELECT 1 
                  FROM   sym_customer2node_active_only s 
                  WHERE  s.customer_id = (inserted.customerid)) 
    AND    1=1 
    INSERT INTO "grips"."dbo".sym_data 
                ( 
                            table_name, 
                            event_type, 
                            trigger_hist_id, 
                            row_data, 
                            channel_id, 
                            transaction_id, 
                            source_node_id, 
                            external_data, 
                            create_time 
                ) 
    SELECT 'GR_CUSTOMERPICTURE', 
           'I', 
           34, 
           CASE 
                  WHEN inserted."sym_row_id" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."sym_row_id") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."Id" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."Id") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."PictureId" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."PictureId") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."CustomerId" IS NULL THEN '' 
                  ELSE ('"' + CONVERT(varchar(40), inserted."CustomerId",2) + '"') 
           END+','+ 
           CASE 
                  WHEN inserted."CreatedDate" IS NULL THEN '' 
                  ELSE ('"' + CONVERT(varchar,inserted."CreatedDate",121) + '"') 
           END+','+ 
           CASE 
                  WHEN inserted."CreatedBy" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."CreatedBy") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."ModifiedDate" IS NULL THEN '' 
                  ELSE ('"' + CONVERT(varchar,inserted."ModifiedDate",121) + '"') 
           END+','+ 
           CASE 
                  WHEN inserted."ModifiedBy" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."ModifiedBy") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."DeletedDate" IS NULL THEN '' 
                  ELSE ('"' + CONVERT(varchar,inserted."DeletedDate",121) + '"') 
           END+','+ 
           CASE 
                  WHEN inserted."DeletedBy" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."DeletedBy") ,'\','\\'),'"','\"') + '"'
           END, 
           'content', 
           @TransactionId, 
           "grips".dbo.sym_node_disabled(), 
           (inserted.customerid), 
           CURRENT_TIMESTAMP 
    FROM   inserted 
    WHERE  EXISTS 
           ( 
                  SELECT 1 
                  FROM   sym_customer2node_active_only s 
                  WHERE  s.customer_id = (inserted.customerid)) 
    AND    1=1 
  END 
  ELSE 
  BEGIN 
    INSERT INTO "grips"."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     'GR_CUSTOMERPICTURE', 
               'U', 
               34, 
               CASE 
                          WHEN inserted."sym_row_id" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."sym_row_id") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."Id" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."Id") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."PictureId" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."PictureId") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."CustomerId" IS NULL THEN '' 
                          ELSE ('"' + CONVERT(varchar(40), inserted."CustomerId",2) + '"') 
               END+','+ 
               CASE 
                          WHEN inserted."CreatedDate" IS NULL THEN '' 
                          ELSE ('"' + CONVERT(varchar,inserted."CreatedDate",121) + '"') 
               END+','+ 
               CASE 
                          WHEN inserted."CreatedBy" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."CreatedBy") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."ModifiedDate" IS NULL THEN '' 
                          ELSE ('"' + CONVERT(varchar,inserted."ModifiedDate",121) + '"') 
               END+','+ 
               CASE 
                          WHEN inserted."ModifiedBy" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."ModifiedBy") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."DeletedDate" IS NULL THEN '' 
                          ELSE ('"' + CONVERT(varchar,inserted."DeletedDate",121) + '"') 
               END+','+ 
               CASE 
                          WHEN inserted."DeletedBy" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."DeletedBy") ,'\','\\'),'"','\"') + '"'
               END, 
               CASE 
                          WHEN deleted."sym_row_id" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(36),deleted."sym_row_id") ,'\','\\'),'"','\"') + '"'
               END, 
               NULL, 
               'content', 
               @TransactionId, 
               "grips".dbo.sym_node_disabled(), 
               (inserted.customerid), 
               CURRENT_TIMESTAMP 
    FROM       inserted 
    INNER JOIN deleted 
    ON         deleted."sym_row_id"=inserted."sym_row_id" 
    WHERE      EXISTS 
               ( 
                      SELECT 1 
                      FROM   sym_customer2node_active_only s 
                      WHERE  s.customer_id = (inserted.customerid)) 
    AND        1=1 
    AND        ((( 
                                                inserted."sym_row_id" IS NOT NULL 
                                     AND        deleted."sym_row_id" IS NOT NULL 
                                     AND        inserted."sym_row_id"<>deleted."sym_row_id") 
                          OR         ( 
                                                inserted."sym_row_id" IS NULL 
                                     AND        deleted."sym_row_id" IS NOT NULL) 
                          OR         ( 
                                                inserted."sym_row_id" IS NOT NULL 
                                     AND        deleted."sym_row_id" IS NULL)) 
               OR         (( 
                                                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."PictureId" IS NOT NULL 
                                     AND        deleted."PictureId" IS NOT NULL 
                                     AND        inserted."PictureId"<>deleted."PictureId") 
                          OR         ( 
                                                inserted."PictureId" IS NULL 
                                     AND        deleted."PictureId" IS NOT NULL) 
                          OR         ( 
                                                inserted."PictureId" IS NOT NULL 
                                     AND        deleted."PictureId" IS NULL)) 
               OR         (( 
                                                inserted."CustomerId" IS NOT NULL 
                                     AND        deleted."CustomerId" IS NOT NULL 
                                     AND        inserted."CustomerId"<>deleted."CustomerId") 
                          OR         ( 
                                                inserted."CustomerId" IS NULL 
                                     AND        deleted."CustomerId" IS NOT NULL) 
                          OR         ( 
                                                inserted."CustomerId" IS NOT NULL 
                                     AND        deleted."CustomerId" IS NULL)) 
               OR         (( 
                                                inserted."CreatedDate" IS NOT NULL 
                                     AND        deleted."CreatedDate" IS NOT NULL 
                                     AND        inserted."CreatedDate"<>deleted."CreatedDate") 
                          OR         ( 
                                                inserted."CreatedDate" IS NULL 
                                     AND        deleted."CreatedDate" IS NOT NULL) 
                          OR         ( 
                                                inserted."CreatedDate" IS NOT NULL 
                                     AND        deleted."CreatedDate" IS NULL)) 
               OR         (( 
                                                inserted."CreatedBy" IS NOT NULL 
                                     AND        deleted."CreatedBy" IS NOT NULL 
                                     AND        inserted."CreatedBy"<>deleted."CreatedBy") 
                          OR         ( 
                                                inserted."CreatedBy" IS NULL 
                                     AND        deleted."CreatedBy" IS NOT NULL) 
                          OR         ( 
                                                inserted."CreatedBy" IS NOT NULL 
                                     AND        deleted."CreatedBy" IS NULL)) 
               OR         (( 
                                                inserted."ModifiedDate" IS NOT NULL 
                                     AND        deleted."ModifiedDate" IS NOT NULL 
                                     AND        inserted."ModifiedDate"<>deleted."ModifiedDate")
                          OR         ( 
                                                inserted."ModifiedDate" IS NULL 
                                     AND        deleted."ModifiedDate" IS NOT NULL) 
                          OR         ( 
                                                inserted."ModifiedDate" IS NOT NULL 
                                     AND        deleted."ModifiedDate" IS NULL)) 
               OR         (( 
                                                inserted."ModifiedBy" IS NOT NULL 
                                     AND        deleted."ModifiedBy" IS NOT NULL 
                                     AND        inserted."ModifiedBy"<>deleted."ModifiedBy") 
                          OR         ( 
                                                inserted."ModifiedBy" IS NULL 
                                     AND        deleted."ModifiedBy" IS NOT NULL) 
                          OR         ( 
                                                inserted."ModifiedBy" IS NOT NULL 
                                     AND        deleted."ModifiedBy" IS NULL)) 
               OR         (( 
                                                inserted."DeletedDate" IS NOT NULL 
                                     AND        deleted."DeletedDate" IS NOT NULL 
                                     AND        inserted."DeletedDate"<>deleted."DeletedDate") 
                          OR         ( 
                                                inserted."DeletedDate" IS NULL 
                                     AND        deleted."DeletedDate" IS NOT NULL) 
                          OR         ( 
                                                inserted."DeletedDate" IS NOT NULL 
                                     AND        deleted."DeletedDate" IS NULL)) 
               OR         (( 
                                                inserted."DeletedBy" IS NOT NULL 
                                     AND        deleted."DeletedBy" IS NOT NULL 
                                     AND        inserted."DeletedBy"<>deleted."DeletedBy") 
                          OR         ( 
                                                inserted."DeletedBy" IS NULL 
                                     AND        deleted."DeletedBy" IS NOT NULL) 
                          OR         ( 
                                                inserted."DeletedBy" IS NOT NULL 
                                     AND        deleted."DeletedBy" IS NULL))) 
  END 
END 
IF (@NCT = 0) 
SET nocount OFF 
END
failing_sql.txt (15,591 bytes)
fixed_sql.txt (15,641 bytes)
--drop trigger sym_on_u_for_gr_cstmrpctr_c2m_cntrl
CREATE TRIGGER sym_on_u_for_gr_cstmrpctr_c2m_cntrl ON "dbo"."gr_CustomerPicture" 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 (dbo.sym_triggers_disabled() = 0) 
BEGIN 
  IF (1=1 
  AND 
  ( 
         UPDATE(sym_row_id) ) ) 
  BEGIN 
    INSERT INTO "grips"."dbo".sym_data 
                ( 
                            table_name, 
                            event_type, 
                            trigger_hist_id, 
                            pk_data, 
                            channel_id, 
                            transaction_id, 
                            source_node_id, 
                            external_data, 
                            create_time 
                ) 
    SELECT 'GR_CUSTOMERPICTURE', 
           'D', 
           34, 
           CASE 
                  WHEN deleted."sym_row_id" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(36),deleted."sym_row_id") ,'\','\\'),'"','\"') + '"'
           END, 
           'content', 
           @TransactionId, 
           "grips".dbo.sym_node_disabled(), 
           (deleted.customerid), 
           CURRENT_TIMESTAMP 
    FROM   deleted 
    WHERE  EXISTS 
           ( 
                  SELECT 1 
                  FROM   sym_customer2node_active_only s 
                  WHERE  s.customer_id = (deleted.customerid)) 
    AND    1=1 
    INSERT INTO "grips"."dbo".sym_data 
                ( 
                            table_name, 
                            event_type, 
                            trigger_hist_id, 
                            row_data, 
                            channel_id, 
                            transaction_id, 
                            source_node_id, 
                            external_data, 
                            create_time 
                ) 
    SELECT 'GR_CUSTOMERPICTURE', 
           'I', 
           34, 
           CASE 
                  WHEN inserted."sym_row_id" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."sym_row_id") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."Id" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."Id") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."PictureId" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."PictureId") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."CustomerId" IS NULL THEN '' 
                  ELSE ('"' + CONVERT(varchar(40), inserted."CustomerId",2) + '"') 
           END+','+ 
           CASE 
                  WHEN inserted."CreatedDate" IS NULL THEN '' 
                  ELSE ('"' + CONVERT(varchar,inserted."CreatedDate",121) + '"') 
           END+','+ 
           CASE 
                  WHEN inserted."CreatedBy" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."CreatedBy") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."ModifiedDate" IS NULL THEN '' 
                  ELSE ('"' + CONVERT(varchar,inserted."ModifiedDate",121) + '"') 
           END+','+ 
           CASE 
                  WHEN inserted."ModifiedBy" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."ModifiedBy") ,'\','\\'),'"','\"') + '"'
           END             +','+ 
           CASE 
                  WHEN inserted."DeletedDate" IS NULL THEN '' 
                  ELSE ('"' + CONVERT(varchar,inserted."DeletedDate",121) + '"') 
           END+','+ 
           CASE 
                  WHEN inserted."DeletedBy" IS NULL THEN '' 
                  ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."DeletedBy") ,'\','\\'),'"','\"') + '"'
           END, 
           'content', 
           @TransactionId, 
           "grips".dbo.sym_node_disabled(), 
           (inserted.customerid), 
           CURRENT_TIMESTAMP 
    FROM   inserted 
    WHERE  EXISTS 
           ( 
                  SELECT 1 
                  FROM   sym_customer2node_active_only s 
                  WHERE  s.customer_id = (inserted.customerid)) 
    AND    1=1 
  END 
  ELSE 
  BEGIN 
    INSERT INTO "grips"."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     'GR_CUSTOMERPICTURE', 
               'U', 
               34, 
               CASE 
                          WHEN inserted."sym_row_id" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."sym_row_id") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."Id" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."Id") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."PictureId" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(36),inserted."PictureId") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."CustomerId" IS NULL THEN '' 
                          ELSE ('"' + CONVERT(varchar(40), inserted."CustomerId",2) + '"') 
               END+','+ 
               CASE 
                          WHEN inserted."CreatedDate" IS NULL THEN '' 
                          ELSE ('"' + CONVERT(varchar,inserted."CreatedDate",121) + '"') 
               END+','+ 
               CASE 
                          WHEN inserted."CreatedBy" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."CreatedBy") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."ModifiedDate" IS NULL THEN '' 
                          ELSE ('"' + CONVERT(varchar,inserted."ModifiedDate",121) + '"') 
               END+','+ 
               CASE 
                          WHEN inserted."ModifiedBy" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."ModifiedBy") ,'\','\\'),'"','\"') + '"'
               END                 +','+ 
               CASE 
                          WHEN inserted."DeletedDate" IS NULL THEN '' 
                          ELSE ('"' + CONVERT(varchar,inserted."DeletedDate",121) + '"') 
               END+','+ 
               CASE 
                          WHEN inserted."DeletedBy" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(255),inserted."DeletedBy") ,'\','\\'),'"','\"') + '"'
               END, 
               CASE 
                          WHEN deleted."sym_row_id" IS NULL THEN '' 
                          ELSE '"' + replace(replace(CONVERT(varchar(36),deleted."sym_row_id") ,'\','\\'),'"','\"') + '"'
               END, 
               NULL, 
               'content', 
               @TransactionId, 
               "grips".dbo.sym_node_disabled(), 
               (inserted.customerid), 
               CURRENT_TIMESTAMP 
    FROM       inserted 
    INNER JOIN deleted 
    ON         deleted."sym_row_id"=inserted."sym_row_id" 
    WHERE      EXISTS 
               ( 
                      SELECT 1 
                      FROM   sym_customer2node_active_only s 
                      WHERE  s.customer_id = (inserted.customerid)) 
    AND        1=1 
    AND        ((( 
                                                inserted."sym_row_id" IS NOT NULL 
                                     AND        deleted."sym_row_id" IS NOT NULL 
                                     AND        inserted."sym_row_id"<>deleted."sym_row_id") 
                          OR         ( 
                                                inserted."sym_row_id" IS NULL 
                                     AND        deleted."sym_row_id" IS NOT NULL) 
                          OR         ( 
                                                inserted."sym_row_id" IS NOT NULL 
                                     AND        deleted."sym_row_id" IS NULL)) 
               OR         (( 
                                                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."PictureId" IS NOT NULL 
                                     AND        deleted."PictureId" IS NOT NULL 
                                     AND        inserted."PictureId"<>deleted."PictureId") 
                          OR         ( 
                                                inserted."PictureId" IS NULL 
                                     AND        deleted."PictureId" IS NOT NULL) 
                          OR         ( 
                                                inserted."PictureId" IS NOT NULL 
                                     AND        deleted."PictureId" IS NULL)) 
               OR         (( 
                                                inserted."CustomerId" IS NOT NULL 
                                     AND        deleted."CustomerId" IS NOT NULL 
                                     AND        inserted."CustomerId"<>deleted."CustomerId") 
                          OR         ( 
                                                inserted."CustomerId" IS NULL 
                                     AND        deleted."CustomerId" IS NOT NULL) 
                          OR         ( 
                                                inserted."CustomerId" IS NOT NULL 
                                     AND        deleted."CustomerId" IS NULL)) 
               OR         (( 
                                                inserted."CreatedDate" IS NOT NULL 
                                     AND        deleted."CreatedDate" IS NOT NULL 
                                     AND        inserted."CreatedDate"<>deleted."CreatedDate") 
                          OR         ( 
                                                inserted."CreatedDate" IS NULL 
                                     AND        deleted."CreatedDate" IS NOT NULL) 
                          OR         ( 
                                                inserted."CreatedDate" IS NOT NULL 
                                     AND        deleted."CreatedDate" IS NULL)) 
               OR         (( 
                                                inserted."CreatedBy" IS NOT NULL 
                                     AND        deleted."CreatedBy" IS NOT NULL 
                                     AND        inserted."CreatedBy"<>deleted."CreatedBy") 
                          OR         ( 
                                                inserted."CreatedBy" IS NULL 
                                     AND        deleted."CreatedBy" IS NOT NULL) 
                          OR         ( 
                                                inserted."CreatedBy" IS NOT NULL 
                                     AND        deleted."CreatedBy" IS NULL)) 
               OR         (( 
                                                inserted."ModifiedDate" IS NOT NULL 
                                     AND        deleted."ModifiedDate" IS NOT NULL 
                                     AND        inserted."ModifiedDate"<>deleted."ModifiedDate")
                          OR         ( 
                                                inserted."ModifiedDate" IS NULL 
                                     AND        deleted."ModifiedDate" IS NOT NULL) 
                          OR         ( 
                                                inserted."ModifiedDate" IS NOT NULL 
                                     AND        deleted."ModifiedDate" IS NULL)) 
               OR         (( 
                                                inserted."ModifiedBy" IS NOT NULL 
                                     AND        deleted."ModifiedBy" IS NOT NULL 
                                     AND        inserted."ModifiedBy"<>deleted."ModifiedBy") 
                          OR         ( 
                                                inserted."ModifiedBy" IS NULL 
                                     AND        deleted."ModifiedBy" IS NOT NULL) 
                          OR         ( 
                                                inserted."ModifiedBy" IS NOT NULL 
                                     AND        deleted."ModifiedBy" IS NULL)) 
               OR         (( 
                                                inserted."DeletedDate" IS NOT NULL 
                                     AND        deleted."DeletedDate" IS NOT NULL 
                                     AND        inserted."DeletedDate"<>deleted."DeletedDate") 
                          OR         ( 
                                                inserted."DeletedDate" IS NULL 
                                     AND        deleted."DeletedDate" IS NOT NULL) 
                          OR         ( 
                                                inserted."DeletedDate" IS NOT NULL 
                                     AND        deleted."DeletedDate" IS NULL)) 
               OR         (( 
                                                inserted."DeletedBy" IS NOT NULL 
                                     AND        deleted."DeletedBy" IS NOT NULL 
                                     AND        inserted."DeletedBy"<>deleted."DeletedBy") 
                          OR         ( 
                                                inserted."DeletedBy" IS NULL 
                                     AND        deleted."DeletedBy" IS NOT NULL) 
                          OR         ( 
                                                inserted."DeletedBy" IS NOT NULL 
                                     AND        deleted."DeletedBy" IS NULL))) 
  END 
END 
IF (@NCT = 0) 
SET nocount OFF 
END
fixed_sql.txt (15,641 bytes)

mkurz

2020-01-06 01:33

reporter   ~0001672

This prevents us from using 3.11.
One idea I had was to use something like:
"select .. from deleted as inserted, deleted as deleted " (sql allows multiple aliases)
But that decreases the performance, because it leads to a join. And if it's
correct in content has to be evaluated.

Issue History

Date Modified Username Field Change
2019-12-12 05:31 mkurz New Issue
2019-12-12 05:31 mkurz File Added: central_3.11_log_snippet.log
2019-12-12 05:31 mkurz File Added: failing_sql.txt
2019-12-12 05:31 mkurz File Added: fixed_sql.txt
2020-01-06 01:33 mkurz Note Added: 0001672
2020-05-22 14:13 elong Target Version => 3.11.10
2020-05-22 14:13 elong Description Updated View Revisions