none
Publishing an SSDT project takes forever after trigger change RRS feed

  • Question

  • We have an SSDT project which we publish to update the database. This has always taken around a minute.

    In order to provide auditing on our tables, we use the script found here, which creates triggers for the table, and inserts an entry, showing the old and new data. A sample trigger looks like this...

    INSERT INTO dbo.Proposals_Audit ([ID],[Active],[Note1Text],
      AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns)
    SELECT CONVERT(int,[ID]) as [ID],[Active],[Note1Text],'New','Update',SUSER_SNAME(),getdate(),
      CASE WHEN UPDATE([ID]) THEN '[ID]-' ELSE '' END + 
      CASE WHEN UPDATE([Active]) THEN '[Active]-' ELSE '' END + 
      CASE WHEN UPDATE([Note1Text]) THEN '[Note1Text]-' ELSE '' END
    FROM INSERTED  

    For clarity, I cut out the lines for all but three of the columns.

    One of the problems with this script is that it uses the UPDATE() function to detect changes, but this doesn't seem to work, and so the audit table shows every column as having changed.

    I modified the script so that it uses his third option, checking if the column appears in the inserted table. The trigger (again, cut down) now looks like this...

    INSERT INTO dbo.Proposals_Audit ([ID],[Active],[Note1Text],
        AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns)
    SELECT CONVERT(int,[ID]) as [ID],[Active],[Note1Text],'New','Update',SUSER_SNAME(),getdate(),''
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ID]<>d.[ID]) then '[ID]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Active]<>d.[Active]) then '[Active]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Note1Text]<>d.[Note1Text]) then '[Note1Text]-' ELSE '' END
    FROM INSERTED  

    This works fine, but when we publish the database, it sticks on the final step for hours (I left it for 18 hours at one point, and it claimed to be still publishing). If we click the Cancel link, it claims to be cancelling, but again, it just sticks there for hours. The only way out of this is to kill VS (you can't close VS while a publish is in progress).

    I narrowed the problem down to one table. If I leave that table's triggers as they were before (using UPDATE()), and modify all the rest to use the new trigger code, the publish takes around a minute. If I update the triggers for this one table, it hangs.

    I'll post the full SQL for the table below, but it looks pretty much like every other table in the database.

    Anyone able to give me any clue as to why this might be happening? It's eminently reproducible.

    Thanks

    Here is the full SQL for the offending table, including the triggers...

    CREATE TABLE [dbo].[Proposals] ( [ID] INT IDENTITY (1, 1) NOT NULL, [Active] BIT CONSTRAINT [DF__VrtQuoteP__Activ__39B0B7DB] DEFAULT ((1)) NOT NULL, [PrimaryProposal] BIT CONSTRAINT [DF__Proposals__Prima__05C6FF81] DEFAULT ((0)) NOT NULL, [VrtQuoteRevisionID] INT NOT NULL, [OptionName] VARCHAR (MAX) CONSTRAINT [DF__Proposals__Optio__2A8E6FDC] DEFAULT ('') NOT NULL, [OfferNotes] VARCHAR (MAX) NULL, [DiscountPercentage] DECIMAL (18, 2) NOT NULL, [ServiceContractPrice] DECIMAL (18, 2) NULL, [ListPrice] DECIMAL (18, 2) CONSTRAINT [DF_Proposals_ListPrice] DEFAULT ((0)) NOT NULL, [OfferPrice] DECIMAL (18, 2) CONSTRAINT [DF_Proposals_OfferPrice] DEFAULT ((0)) NOT NULL, [WarrantyMonths] INT CONSTRAINT [DF_Proposals_WarrantyMonths] DEFAULT ((12)) NOT NULL, [ApplyDeadlineDiscount] BIT CONSTRAINT [DF_Proposals_ApplyDeadlineDiscount] DEFAULT ((0)) NOT NULL, [DeadlineDiscountDate] DATETIME NULL, [DeadlineDiscountPrice] DECIMAL (18, 2) CONSTRAINT [DF_Proposals_DeadlineDiscountPrice] DEFAULT ((0)) NOT NULL, [PaymentAgreementID] INT CONSTRAINT [DF_Proposals_PaymentAgreementID] DEFAULT ((12)) NOT NULL, [PaymentAgreementCustom] BIT CONSTRAINT [DF_Proposals_PaymentAgreementCustom] DEFAULT ((0)) NOT NULL, [PaymentAgreementCustomText] VARCHAR (MAX) CONSTRAINT [DF_Proposals_PaymentAgreementCustomText] DEFAULT ('') NOT NULL, [IncludeServiceOffer] BIT CONSTRAINT [DF_Proposals_IncludeServiceOffer] DEFAULT ((0)) NOT NULL, [TimeDependantDiscountPhrase1] VARCHAR (MAX) CONSTRAINT [DF_Proposals_TimeDependantDiscountPhrase1] DEFAULT ('') NOT NULL, [TimeDependantDiscountPhrase2] VARCHAR (MAX) CONSTRAINT [DF_Proposals_TimeDependantDiscountPhrase2] DEFAULT ('') NOT NULL, [TimeDependantDiscountPhrase3] VARCHAR (MAX) CONSTRAINT [DF_Proposals_TimeDependantDiscountPhrase3] DEFAULT ('') NOT NULL, [IncludeShippingAndTax] BIT CONSTRAINT [DF_Proposals_IncludeShippingAndTax] DEFAULT ((0)) NOT NULL, [Price1Yr] DECIMAL (18, 2) CONSTRAINT [DF_Proposals_Price1Yr] DEFAULT ((0)) NOT NULL, [Discount1Yr] DECIMAL (18, 2) CONSTRAINT [DF_Proposals_Discount1Yr] DEFAULT ((0)) NOT NULL, [Discount3Yr] DECIMAL (18, 2) CONSTRAINT [DF_Proposals_Discount3Yr] DEFAULT ((0)) NOT NULL, [Discount5Yr] DECIMAL (18, 2) CONSTRAINT [DF_Proposals_Discount5Yr] DEFAULT ((0)) NOT NULL, [ApprovalReasons] NVARCHAR (MAX) CONSTRAINT [DF_Proposals_ApprovalReasons] DEFAULT ('') NOT NULL, [ExternalVersion] INT CONSTRAINT [DF_Proposals_ExternalVersion] DEFAULT ((0)) NOT NULL, [ApprovalStatusId] INT CONSTRAINT [DF__Proposals__Appro__1053A569] DEFAULT ((0)) NOT NULL, [ApproveUserId] INT NULL, [ApproveRequestByUserId] INT NULL, [RejectUserId] INT NULL, [ApproveRequestDate] DATETIME NULL, [ApproveRequestToUserId] INT NULL, [IsStandardNotesIncluded] BIT NULL, [ParagraphTermsAndConditions] VARCHAR (MAX) DEFAULT ('') NOT NULL, [ParagraphWarrantyPeriod] VARCHAR (MAX) DEFAULT ('') NOT NULL, [ParagraphProductSupport] VARCHAR (MAX) DEFAULT ('') NOT NULL, [Note1Text] VARCHAR (MAX) DEFAULT ('') NOT NULL, CONSTRAINT [PK__VrtQuote__3214EC2737C86F69] PRIMARY KEY CLUSTERED ([ID] ASC), CONSTRAINT [FK__Proposals__VrtQu__23E1724D] FOREIGN KEY ([VrtQuoteRevisionID]) REFERENCES [dbo].[VrtQuoteRevisions] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT [FK_Proposals_ApproveRequestByUsers] FOREIGN KEY ([ApproveRequestByUserId]) REFERENCES [dbo].[Users] ([ID]), CONSTRAINT [FK_Proposals_ApproveRequestToUsers] FOREIGN KEY ([ApproveRequestToUserId]) REFERENCES [dbo].[Users] ([ID]), CONSTRAINT [FK_Proposals_ApproveUsers] FOREIGN KEY ([ApproveUserId]) REFERENCES [dbo].[Users] ([ID]), CONSTRAINT [FK_Proposals_PaymentAgreements] FOREIGN KEY ([PaymentAgreementID]) REFERENCES [dbo].[PaymentAgreements] ([ID]), CONSTRAINT [FK_Proposals_RejectUsers] FOREIGN KEY ([RejectUserId]) REFERENCES [dbo].[Users] ([ID]) );

    GO CREATE TRIGGER Proposals_Delete ON dbo.Proposals FOR DELETE AS INSERT INTO dbo.Proposals_Audit ([ID],[Active],[PrimaryProposal],[VrtQuoteRevisionID],[OptionName],[OfferNotes],[DiscountPercentage],[ServiceContractPrice],[ListPrice],[OfferPrice],[WarrantyMonths],[ApplyDeadlineDiscount],[DeadlineDiscountDate],[DeadlineDiscountPrice],[PaymentAgreementID],[PaymentAgreementCustom],[PaymentAgreementCustomText],[IncludeServiceOffer],[TimeDependantDiscountPhrase1],[TimeDependantDiscountPhrase2],[TimeDependantDiscountPhrase3],[IncludeShippingAndTax],[Price1Yr],[Discount1Yr],[Discount3Yr],[Discount5Yr],[ApprovalReasons],[ExternalVersion],[ApprovalStatusId],[ApproveUserId],[ApproveRequestByUserId],[RejectUserId],[ApproveRequestDate],[ApproveRequestToUserId],[IsStandardNotesIncluded],[ParagraphTermsAndConditions],[ParagraphWarrantyPeriod],[ParagraphProductSupport],[Note1Text],AuditDataState ,AuditDMLAction ,AuditUser ,AuditDateTime ,UpdateColumns ) SELECT CONVERT(int,[ID]) as [ID],[Active],[PrimaryProposal],[VrtQuoteRevisionID],[OptionName],[OfferNotes],[DiscountPercentage],[ServiceContractPrice],[ListPrice],[OfferPrice],[WarrantyMonths],[ApplyDeadlineDiscount],[DeadlineDiscountDate],[DeadlineDiscountPrice],[PaymentAgreementID],[PaymentAgreementCustom],[PaymentAgreementCustomText],[IncludeServiceOffer],[TimeDependantDiscountPhrase1],[TimeDependantDiscountPhrase2],[TimeDependantDiscountPhrase3],[IncludeShippingAndTax],[Price1Yr],[Discount1Yr],[Discount3Yr],[Discount5Yr],[ApprovalReasons],[ExternalVersion],[ApprovalStatusId],[ApproveUserId],[ApproveRequestByUserId],[RejectUserId],[ApproveRequestDate],[ApproveRequestToUserId],[IsStandardNotesIncluded],[ParagraphTermsAndConditions],[ParagraphWarrantyPeriod],[ParagraphProductSupport],[Note1Text],'Old','Delete',SUSER_SNAME(),getdate(),'' FROM DELETED GO CREATE TRIGGER Proposals_Insert ON dbo.Proposals FOR INSERT AS INSERT INTO dbo.Proposals_Audit ([ID],[Active],[PrimaryProposal],[VrtQuoteRevisionID],[OptionName],[OfferNotes],[DiscountPercentage],[ServiceContractPrice],[ListPrice],[OfferPrice],[WarrantyMonths],[ApplyDeadlineDiscount],[DeadlineDiscountDate],[DeadlineDiscountPrice],[PaymentAgreementID],[PaymentAgreementCustom],[PaymentAgreementCustomText],[IncludeServiceOffer],[TimeDependantDiscountPhrase1],[TimeDependantDiscountPhrase2],[TimeDependantDiscountPhrase3],[IncludeShippingAndTax],[Price1Yr],[Discount1Yr],[Discount3Yr],[Discount5Yr],[ApprovalReasons],[ExternalVersion],[ApprovalStatusId],[ApproveUserId],[ApproveRequestByUserId],[RejectUserId],[ApproveRequestDate],[ApproveRequestToUserId],[IsStandardNotesIncluded],[ParagraphTermsAndConditions],[ParagraphWarrantyPeriod],[ParagraphProductSupport],[Note1Text],AuditDataState ,AuditDMLAction ,AuditUser ,AuditDateTime ,UpdateColumns ) SELECT CONVERT(int,[ID]) as [ID],[Active],[PrimaryProposal],[VrtQuoteRevisionID],[OptionName],[OfferNotes],[DiscountPercentage],[ServiceContractPrice],[ListPrice],[OfferPrice],[WarrantyMonths],[ApplyDeadlineDiscount],[DeadlineDiscountDate],[DeadlineDiscountPrice],[PaymentAgreementID],[PaymentAgreementCustom],[PaymentAgreementCustomText],[IncludeServiceOffer],[TimeDependantDiscountPhrase1],[TimeDependantDiscountPhrase2],[TimeDependantDiscountPhrase3],[IncludeShippingAndTax],[Price1Yr],[Discount1Yr],[Discount3Yr],[Discount5Yr],[ApprovalReasons],[ExternalVersion],[ApprovalStatusId],[ApproveUserId],[ApproveRequestByUserId],[RejectUserId],[ApproveRequestDate],[ApproveRequestToUserId],[IsStandardNotesIncluded],[ParagraphTermsAndConditions],[ParagraphWarrantyPeriod],[ParagraphProductSupport],[Note1Text],'New','Insert',SUSER_SNAME(),getdate(),'' FROM INSERTED GO CREATE TRIGGER Proposals_Update ON dbo.Proposals FOR UPDATE AS INSERT INTO dbo.Proposals_Audit ([ID],[Active],[PrimaryProposal],[VrtQuoteRevisionID],[OptionName],[OfferNotes],[DiscountPercentage],[ServiceContractPrice],[ListPrice],[OfferPrice],[WarrantyMonths],[ApplyDeadlineDiscount],[DeadlineDiscountDate],[DeadlineDiscountPrice],[PaymentAgreementID],[PaymentAgreementCustom],[PaymentAgreementCustomText],[IncludeServiceOffer],[TimeDependantDiscountPhrase1],[TimeDependantDiscountPhrase2],[TimeDependantDiscountPhrase3],[IncludeShippingAndTax],[Price1Yr],[Discount1Yr],[Discount3Yr],[Discount5Yr],[ApprovalReasons],[ExternalVersion],[ApprovalStatusId],[ApproveUserId],[ApproveRequestByUserId],[RejectUserId],[ApproveRequestDate],[ApproveRequestToUserId],[IsStandardNotesIncluded],[ParagraphTermsAndConditions],[ParagraphWarrantyPeriod],[ParagraphProductSupport],[Note1Text],AuditDataState ,AuditDMLAction ,AuditUser ,AuditDateTime ,UpdateColumns ) SELECT CONVERT(int,[ID]) as [ID],[Active],[PrimaryProposal],[VrtQuoteRevisionID],[OptionName],[OfferNotes],[DiscountPercentage],[ServiceContractPrice],[ListPrice],[OfferPrice],[WarrantyMonths],[ApplyDeadlineDiscount],[DeadlineDiscountDate],[DeadlineDiscountPrice],[PaymentAgreementID],[PaymentAgreementCustom],[PaymentAgreementCustomText],[IncludeServiceOffer],[TimeDependantDiscountPhrase1],[TimeDependantDiscountPhrase2],[TimeDependantDiscountPhrase3],[IncludeShippingAndTax],[Price1Yr],[Discount1Yr],[Discount3Yr],[Discount5Yr],[ApprovalReasons],[ExternalVersion],[ApprovalStatusId],[ApproveUserId],[ApproveRequestByUserId],[RejectUserId],[ApproveRequestDate],[ApproveRequestToUserId],[IsStandardNotesIncluded],[ParagraphTermsAndConditions],[ParagraphWarrantyPeriod],[ParagraphProductSupport],[Note1Text],'New','Update',SUSER_SNAME(),getdate(),'' + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ID]<>d.[ID]) then '[ID]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Active]<>d.[Active]) then '[Active]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[PrimaryProposal]<>d.[PrimaryProposal]) then '[PrimaryProposal]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[VrtQuoteRevisionID]<>d.[VrtQuoteRevisionID]) then '[VrtQuoteRevisionID]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[OptionName]<>d.[OptionName]) then '[OptionName]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[OfferNotes]<>d.[OfferNotes]) then '[OfferNotes]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[DiscountPercentage]<>d.[DiscountPercentage]) then '[DiscountPercentage]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ServiceContractPrice]<>d.[ServiceContractPrice]) then '[ServiceContractPrice]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ListPrice]<>d.[ListPrice]) then '[ListPrice]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[OfferPrice]<>d.[OfferPrice]) then '[OfferPrice]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[WarrantyMonths]<>d.[WarrantyMonths]) then '[WarrantyMonths]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApplyDeadlineDiscount]<>d.[ApplyDeadlineDiscount]) then '[ApplyDeadlineDiscount]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[DeadlineDiscountDate]<>d.[DeadlineDiscountDate]) then '[DeadlineDiscountDate]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[DeadlineDiscountPrice]<>d.[DeadlineDiscountPrice]) then '[DeadlineDiscountPrice]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[PaymentAgreementID]<>d.[PaymentAgreementID]) then '[PaymentAgreementID]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[PaymentAgreementCustom]<>d.[PaymentAgreementCustom]) then '[PaymentAgreementCustom]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[PaymentAgreementCustomText]<>d.[PaymentAgreementCustomText]) then '[PaymentAgreementCustomText]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[IncludeServiceOffer]<>d.[IncludeServiceOffer]) then '[IncludeServiceOffer]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[TimeDependantDiscountPhrase1]<>d.[TimeDependantDiscountPhrase1]) then '[TimeDependantDiscountPhrase1]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[TimeDependantDiscountPhrase2]<>d.[TimeDependantDiscountPhrase2]) then '[TimeDependantDiscountPhrase2]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[TimeDependantDiscountPhrase3]<>d.[TimeDependantDiscountPhrase3]) then '[TimeDependantDiscountPhrase3]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[IncludeShippingAndTax]<>d.[IncludeShippingAndTax]) then '[IncludeShippingAndTax]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Price1Yr]<>d.[Price1Yr]) then '[Price1Yr]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Discount1Yr]<>d.[Discount1Yr]) then '[Discount1Yr]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Discount3Yr]<>d.[Discount3Yr]) then '[Discount3Yr]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Discount5Yr]<>d.[Discount5Yr]) then '[Discount5Yr]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApprovalReasons]<>d.[ApprovalReasons]) then '[ApprovalReasons]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ExternalVersion]<>d.[ExternalVersion]) then '[ExternalVersion]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApprovalStatusId]<>d.[ApprovalStatusId]) then '[ApprovalStatusId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApproveUserId]<>d.[ApproveUserId]) then '[ApproveUserId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApproveRequestByUserId]<>d.[ApproveRequestByUserId]) then '[ApproveRequestByUserId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[RejectUserId]<>d.[RejectUserId]) then '[RejectUserId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApproveRequestDate]<>d.[ApproveRequestDate]) then '[ApproveRequestDate]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApproveRequestToUserId]<>d.[ApproveRequestToUserId]) then '[ApproveRequestToUserId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[IsStandardNotesIncluded]<>d.[IsStandardNotesIncluded]) then '[IsStandardNotesIncluded]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ParagraphTermsAndConditions]<>d.[ParagraphTermsAndConditions]) then '[ParagraphTermsAndConditions]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ParagraphWarrantyPeriod]<>d.[ParagraphWarrantyPeriod]) then '[ParagraphWarrantyPeriod]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ParagraphProductSupport]<>d.[ParagraphProductSupport]) then '[ParagraphProductSupport]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Note1Text]<>d.[Note1Text]) then '[Note1Text]-' ELSE '' END FROM INSERTED INSERT INTO dbo.Proposals_Audit ([ID],[Active],[PrimaryProposal],[VrtQuoteRevisionID],[OptionName],[OfferNotes],[DiscountPercentage],[ServiceContractPrice],[ListPrice],[OfferPrice],[WarrantyMonths],[ApplyDeadlineDiscount],[DeadlineDiscountDate],[DeadlineDiscountPrice],[PaymentAgreementID],[PaymentAgreementCustom],[PaymentAgreementCustomText],[IncludeServiceOffer],[TimeDependantDiscountPhrase1],[TimeDependantDiscountPhrase2],[TimeDependantDiscountPhrase3],[IncludeShippingAndTax],[Price1Yr],[Discount1Yr],[Discount3Yr],[Discount5Yr],[ApprovalReasons],[ExternalVersion],[ApprovalStatusId],[ApproveUserId],[ApproveRequestByUserId],[RejectUserId],[ApproveRequestDate],[ApproveRequestToUserId],[IsStandardNotesIncluded],[ParagraphTermsAndConditions],[ParagraphWarrantyPeriod],[ParagraphProductSupport],[Note1Text],AuditDataState ,AuditDMLAction ,AuditUser ,AuditDateTime ,UpdateColumns ) SELECT CONVERT(int,[ID]) as [ID],[Active],[PrimaryProposal],[VrtQuoteRevisionID],[OptionName],[OfferNotes],[DiscountPercentage],[ServiceContractPrice],[ListPrice],[OfferPrice],[WarrantyMonths],[ApplyDeadlineDiscount],[DeadlineDiscountDate],[DeadlineDiscountPrice],[PaymentAgreementID],[PaymentAgreementCustom],[PaymentAgreementCustomText],[IncludeServiceOffer],[TimeDependantDiscountPhrase1],[TimeDependantDiscountPhrase2],[TimeDependantDiscountPhrase3],[IncludeShippingAndTax],[Price1Yr],[Discount1Yr],[Discount3Yr],[Discount5Yr],[ApprovalReasons],[ExternalVersion],[ApprovalStatusId],[ApproveUserId],[ApproveRequestByUserId],[RejectUserId],[ApproveRequestDate],[ApproveRequestToUserId],[IsStandardNotesIncluded],[ParagraphTermsAndConditions],[ParagraphWarrantyPeriod],[ParagraphProductSupport],[Note1Text],'Old','Update',SUSER_SNAME(),getdate(),'' + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ID]<>d.[ID]) then '[ID]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Active]<>d.[Active]) then '[Active]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[PrimaryProposal]<>d.[PrimaryProposal]) then '[PrimaryProposal]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[VrtQuoteRevisionID]<>d.[VrtQuoteRevisionID]) then '[VrtQuoteRevisionID]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[OptionName]<>d.[OptionName]) then '[OptionName]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[OfferNotes]<>d.[OfferNotes]) then '[OfferNotes]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[DiscountPercentage]<>d.[DiscountPercentage]) then '[DiscountPercentage]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ServiceContractPrice]<>d.[ServiceContractPrice]) then '[ServiceContractPrice]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ListPrice]<>d.[ListPrice]) then '[ListPrice]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[OfferPrice]<>d.[OfferPrice]) then '[OfferPrice]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[WarrantyMonths]<>d.[WarrantyMonths]) then '[WarrantyMonths]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApplyDeadlineDiscount]<>d.[ApplyDeadlineDiscount]) then '[ApplyDeadlineDiscount]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[DeadlineDiscountDate]<>d.[DeadlineDiscountDate]) then '[DeadlineDiscountDate]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[DeadlineDiscountPrice]<>d.[DeadlineDiscountPrice]) then '[DeadlineDiscountPrice]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[PaymentAgreementID]<>d.[PaymentAgreementID]) then '[PaymentAgreementID]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[PaymentAgreementCustom]<>d.[PaymentAgreementCustom]) then '[PaymentAgreementCustom]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[PaymentAgreementCustomText]<>d.[PaymentAgreementCustomText]) then '[PaymentAgreementCustomText]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[IncludeServiceOffer]<>d.[IncludeServiceOffer]) then '[IncludeServiceOffer]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[TimeDependantDiscountPhrase1]<>d.[TimeDependantDiscountPhrase1]) then '[TimeDependantDiscountPhrase1]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[TimeDependantDiscountPhrase2]<>d.[TimeDependantDiscountPhrase2]) then '[TimeDependantDiscountPhrase2]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[TimeDependantDiscountPhrase3]<>d.[TimeDependantDiscountPhrase3]) then '[TimeDependantDiscountPhrase3]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[IncludeShippingAndTax]<>d.[IncludeShippingAndTax]) then '[IncludeShippingAndTax]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Price1Yr]<>d.[Price1Yr]) then '[Price1Yr]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Discount1Yr]<>d.[Discount1Yr]) then '[Discount1Yr]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Discount3Yr]<>d.[Discount3Yr]) then '[Discount3Yr]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Discount5Yr]<>d.[Discount5Yr]) then '[Discount5Yr]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApprovalReasons]<>d.[ApprovalReasons]) then '[ApprovalReasons]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ExternalVersion]<>d.[ExternalVersion]) then '[ExternalVersion]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApprovalStatusId]<>d.[ApprovalStatusId]) then '[ApprovalStatusId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApproveUserId]<>d.[ApproveUserId]) then '[ApproveUserId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApproveRequestByUserId]<>d.[ApproveRequestByUserId]) then '[ApproveRequestByUserId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[RejectUserId]<>d.[RejectUserId]) then '[RejectUserId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApproveRequestDate]<>d.[ApproveRequestDate]) then '[ApproveRequestDate]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ApproveRequestToUserId]<>d.[ApproveRequestToUserId]) then '[ApproveRequestToUserId]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[IsStandardNotesIncluded]<>d.[IsStandardNotesIncluded]) then '[IsStandardNotesIncluded]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ParagraphTermsAndConditions]<>d.[ParagraphTermsAndConditions]) then '[ParagraphTermsAndConditions]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ParagraphWarrantyPeriod]<>d.[ParagraphWarrantyPeriod]) then '[ParagraphWarrantyPeriod]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ParagraphProductSupport]<>d.[ParagraphProductSupport]) then '[ParagraphProductSupport]-' ELSE '' END + + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Note1Text]<>d.[Note1Text]) then '[Note1Text]-' ELSE '' END FROM DELETED

    Just in case it helps, here is the full SQL for another table which doesn't affect the publish...

    CREATE TABLE [dbo].[Cities] (
        [ID]                  INT           IDENTITY (1, 1) NOT NULL,
        [Name]                VARCHAR (150) COLLATE Latin1_General_CI_AI CONSTRAINT [DF__Cities__Name__0D4825B8] DEFAULT ('') NOT NULL,
        [RegionID]            INT           NULL,
        [Active]              BIT           CONSTRAINT [DF__Cities__Activ__10249263] DEFAULT ((1)) NOT NULL,
        [TimeStamp]           DATETIME      CONSTRAINT [DF__Cities__TimeS__1118B69C] DEFAULT (getdate()) NOT NULL,
        [LastUpdatedByUserID] INT           DEFAULT ((0)) NOT NULL,
        CONSTRAINT [ID] PRIMARY KEY CLUSTERED ([ID] ASC),
        CONSTRAINT [FK_Cities_Regions] FOREIGN KEY ([RegionID]) REFERENCES [dbo].[Regions] ([ID])
    );
    
    GO
     
    CREATE TRIGGER Cities_Update 
    ON dbo.Cities 
    FOR UPDATE 
    AS 
     
     INSERT INTO dbo.Cities_Audit ([ID],[Name],[RegionID],[Active],[TimeStamp],[LastUpdatedByUserID],AuditDataState
      ,AuditDMLAction 
      ,AuditUser
      ,AuditDateTime 
      ,UpdateColumns 
     )
     SELECT CONVERT(int,[ID]) as [ID],[Name],[RegionID],[Active],[TimeStamp],[LastUpdatedByUserID],'New','Update',SUSER_SNAME(),getdate(),  + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ID]<>d.[ID]) then '[ID]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Name]<>d.[Name]) then '[Name]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[RegionID]<>d.[RegionID]) then '[RegionID]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Active]<>d.[Active]) then '[Active]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[TimeStamp]<>d.[TimeStamp]) then '[TimeStamp]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[LastUpdatedByUserID]<>d.[LastUpdatedByUserID]) then '[LastUpdatedByUserID]-' ELSE '' END   FROM INSERTED  
     
     INSERT INTO dbo.Cities_Audit ([ID],[Name],[RegionID],[Active],[TimeStamp],[LastUpdatedByUserID],AuditDataState
      ,AuditDMLAction 
      ,AuditUser
      ,AuditDateTime 
      ,UpdateColumns 
     )
     SELECT CONVERT(int,[ID]) as [ID],[Name],[RegionID],[Active],[TimeStamp],[LastUpdatedByUserID],'Old','Update',SUSER_SNAME(),getdate(),  + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[ID]<>d.[ID]) then '[ID]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Name]<>d.[Name]) then '[Name]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[RegionID]<>d.[RegionID]) then '[RegionID]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[Active]<>d.[Active]) then '[Active]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[TimeStamp]<>d.[TimeStamp]) then '[TimeStamp]-' ELSE '' END + 
     + case when exists (select 1 from inserted i inner join deleted d on i.ID=d.ID where i.[LastUpdatedByUserID]<>d.[LastUpdatedByUserID]) then '[LastUpdatedByUserID]-' ELSE '' END   FROM DELETED
    GO
     
    CREATE TRIGGER Cities_Insert 
    ON dbo.Cities 
    FOR INSERT 
    AS 
     
     INSERT INTO dbo.Cities_Audit ([ID],[Name],[RegionID],[Active],[TimeStamp],[LastUpdatedByUserID],AuditDataState
      ,AuditDMLAction 
      ,AuditUser
      ,AuditDateTime 
      ,UpdateColumns 
     )
     SELECT CONVERT(int,[ID]) as [ID],[Name],[RegionID],[Active],[TimeStamp],[LastUpdatedByUserID],'New','Insert',SUSER_SNAME(),getdate(),''  FROM INSERTED
    GO
     
    CREATE TRIGGER Cities_Delete 
    ON dbo.Cities 
    FOR DELETE 
    AS 
     
     INSERT INTO dbo.Cities_Audit ([ID],[Name],[RegionID],[Active],[TimeStamp],[LastUpdatedByUserID],AuditDataState
      ,AuditDMLAction 
      ,AuditUser
      ,AuditDateTime 
      ,UpdateColumns 
     )
     SELECT CONVERT(int,[ID]) as [ID],[Name],[RegionID],[Active],[TimeStamp],[LastUpdatedByUserID],'Old','Delete',SUSER_SNAME(),getdate(),''  FROM DELETED


    • Edited by Mr Yossu Monday, September 9, 2019 4:40 PM
    Monday, September 9, 2019 4:39 PM

All replies

  • UPDATE() does not mean what you think.  It checks if the column was in the INSERT/UPDATE statement, NOT if the field actually changed value.  There is nothing built into SQL Server which tells you if the value actually changed or not.

    You do not want to do that level of work at the trigger level.  You are better off making the trigger just copy the entire row and then do that analysis when you query the audit table.

    I suggest you read this:

    https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/

    Monday, September 9, 2019 6:08 PM
  • This is a lighter way to do what you are doing in your post.  However, this kind of audit is hard to report on long term.

    https://www.red-gate.com/simple-talk/sql/database-administration/pop-rivetts-sql-server-faq-no-5-pop-on-the-audit-trail/

    Monday, September 9, 2019 6:14 PM
  • Hello Tom,

    Thanks for both replies, very interesting reading. However, neither of them answer my basic question, which is why the publish hangs. As far as I understand it, publishing doesn't alter any data in the tables (and running a profiler while it publishes confirms this), so changing the triggers shouldn't actually make any difference at all. I can't understand why changing the triggers makes any difference.

    Thanks again, any ideas?

    Monday, September 9, 2019 8:12 PM
  • Look at your deployment log. It must be updating the something in the table causing the trigger to run.  Possibly in a post-deployment script.

    Tuesday, September 10, 2019 11:35 AM
  • Look at your deployment log. It must be updating the something in the table causing the trigger to run.  Possibly in a post-deployment script.

    Well, the pd script does update this table, but I don't see why that would cause the problem. The table only has 15K rows, which is not such a lot. Running the pd script manually in SSMS takes a few seconds, and as it has been run so many times during investigation of the problem, it's not updating any data anymore (there WHERE clauses don't match any data as it's all been updated already), so the audit tables don't get any new rows.

    By contrast, when I publish the database, it can sit there for hours. The other day I left it overnight, and due to being delayed back, it ran for 18 hours before I killed VS. I can't believe that a pd script that runs in seconds through SSMS would require over 18 hours to run when published via VS.

    Also, if I look at the profiler while publishing, it doesn't show any database activity at all during the publish phase. That's what puzzles me so much, so it really doesn't look like the pd script that's the culprit.

    Thanks for the reply. Any other ideas?

    P.S. What did you mean by deployment log? Where would I find one of those?

    Tuesday, September 10, 2019 1:45 PM
  • How exactly are you "publishing"?  Are you using VS and right clicking and doing "Publish..."  or using SQLPackage.exe?

    In either case, there is a log created of what it is doing.

    In your case, it sounds more like it is blocked by other usage and cannot complete.  I would look at the server and see what the waitstate on the deployment spid is.

    Tuesday, September 10, 2019 4:44 PM
  • How exactly are you "publishing"?  Are you using VS and right clicking and doing "Publish..."  or using SQLPackage.exe?

    In VS, right-click and Publish.

    In either case, there is a log created of what it is doing.

    Where would I find it? As it happens, I think I found the problem (see below), but it would be useful to know where the log is.

    In your case, it sounds more like it is blocked by other usage and cannot complete.  I would look at the server and see what the waitstate on the deployment spid is.

    Well, I was just about to get out the ketchup and eat my hat, as it turned out the problem was in post-sync, but not where I expected. There was an old PS script being called, and there is a query in there which seems to be the culprit. I'm running it in SSMS as we speak, and it's been running for about 5 minutes already with no sign of stopping.

    So, I think I have my answer. Thanks again for the help. If you could help me find the log file, it would be great.

    Tuesday, September 10, 2019 4:51 PM
  • In VS, open the "Data Tools Operations" window and click "View Results".


    • Edited by Tom Phillips Tuesday, September 10, 2019 6:47 PM
    Tuesday, September 10, 2019 6:47 PM
  • In VS, open the "Data Tools Operations" window and click "View Results".


    Where does it show a log? I see a "View preview" link, and a "View results" link when the publish has finished, which shows the same script, but with messages below. Is that what you meant by the log? If so, it's only available after the publish completes, which in my case wasn't happening, so I never got that link.

    Thanks again

    Wednesday, September 11, 2019 1:20 PM
  • Yes, "View Results" is the publish log I was referring too and it only shows when it is complete.

    If you stop the deployment, you can click the view results and see what it was doing.

    You can also click "Generate Script", instead of "Publish", and run the script manually in SSMS to see what it is doing.

    Wednesday, September 11, 2019 1:47 PM
  • Yes, "View Results" is the publish log I was referring too and it only shows when it is complete.

    If you stop the deployment, you can click the view results and see what it was doing.

    You can also click "Generate Script", instead of "Publish", and run the script manually in SSMS to see what it is doing.

    As mentioned in my first post, clicking the link to stop the publish didn't do anything. It still sat there for hours on end until I killed VS, so I never got to the see the results.

    Clicking Generate Script shows you the same as View Preview, but that didn't really help, as I needed to see where the script stopped. That's where a log file would ahve been useful.

    Thanks

    Wednesday, September 11, 2019 1:49 PM