How can we help you today? How can we help you today?

SQL Compare, add a column to triggered %history table

Please help me configure SQL Compare to effectively deal with adding a column to the parent table of a trigger-maintained history table; i.e.  a trigger on TableA that writes to TableA_History.

SQL Compare doesn't seem to preserve the data?  Making no attempts whatsoever?

I can't use SQL Compare to add a column to the parent table (TableA) that participates in a parent-child relationship with the TableA_History table.

Without SQL Compare to automate these changes, I am hacking and whacking where I thought that I was paying Red Gate to avoid hacking and whacking.

 

/*

Add new column ADname to YesDepts

*/

--    fresh start
DROP TABLE IF EXISTS [dbo].[o_YesDepts];

DROP TABLE IF EXISTS [dbo].[o_YesDepts_History];


--    copy YesDepts and YesDepts_History
SELECT *
INTO o_YesDepts
FROM YesDepts;
GO

SELECT *
INTO o_YesDepts_History
FROM YesDepts_History;
GO

 

--    drop the trigger
DROP TRIGGER IF EXISTS [dbo].[trg_YesDeptsHistory]
GO


--    FK drop, DV constraints disappear when the table is dropped
--    EXEC sp_fkeys 'YesDepts'

ALTER TABLE [dbo].[UserAccessLevels] DROP CONSTRAINT [FK__UserAcces__DeptI__7E37BEF6]
GO

ALTER TABLE [dbo].[UserTempAccessLevels] DROP CONSTRAINT [FK_UserTempAccessLevels_DeptID]
GO

ALTER TABLE [dbo].[UserTempAccessLevels_og] DROP CONSTRAINT [FK__UserTempA__DeptI__06CD04F7]
GO

 

--    DROP YesDepts
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[o_YesDepts]') AND type in (N'U'))
DROP TABLE [dbo].[YesDepts]
GO

--    DROP YesDepts_History
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[o_YesDepts_History]') AND type in (N'U'))
DROP TABLE [dbo].[YesDepts_History]
GO

 

What SQL Compare does not do…

--    CREATE NEW YesDepts

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[YesDepts](
    [YesDeptID] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [nvarchar](255) NOT NULL,
    [BUN] [nvarchar](255) NOT NULL,
    [ADName] [nvarchar](255) NULL,
    [ServiceNowName] [nvarchar](255) NULL,
    [EffectiveDate] [datetime] NOT NULL,
    [ExpirationDate] [datetime] NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [Active] [int] NOT NULL,
CONSTRAINT [PK_YesDepts_YesDeptID] PRIMARY KEY CLUSTERED 
(
    [YesDeptID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[YesDepts] ADD  CONSTRAINT [DV_YesDepts_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[YesDepts] ADD  CONSTRAINT [DV_YesDepts_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

 


--    CREATE NEW YesDepts_History
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[YesDepts_History](
    [YesDeptsHistoryID] [int] IDENTITY(1,1) NOT NULL,
    [YesDeptID] [int] NULL,
    [DepartmentName_old] [nvarchar](255) NULL,
    [DepartmentName_new] [nvarchar](255) NULL,
    [BUN_old] [nvarchar](255) NULL,
    [BUN_new] [nvarchar](255) NULL,
    [ADName_old] [nvarchar](255) NULL,
    [ADName_new] [nvarchar](255) NULL,
    [ServiceNowName_old] [nvarchar](255) NULL,
    [ServiceNowName_new] [nvarchar](255) NULL,
    [EffectiveDate_old] [datetime] NULL,
    [EffectiveDate_new] [datetime] NULL,
    [ExpirationDate_old] [datetime] NULL,
    [ExpirationDate_new] [datetime] NULL,
    [CreatedDate_old] [datetime] NULL,
    [CreatedDate_new] [datetime] NULL,
    [ModifiedDate_old] [datetime] NULL,
    [ModifiedDate_new] [datetime] NULL,
    [Active_old] [int] NULL,
    [Active_new] [int] NULL,
    [Changed_By] [nvarchar](255) NULL,
    [Changed_At] [datetime] NULL,
    [Change_Type] [nvarchar](255) NULL,
CONSTRAINT [PK_YesDeptsHistoryID] PRIMARY KEY CLUSTERED 
(
    [YesDeptsHistoryID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

 

 


--    RECREATE the trigger !!!!!!!!!!!!!!DISABLED!!!!!!!!!!!!!!!

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 

CREATE TRIGGER [dbo].[trg_YesDeptsHistory] on [dbo].[YesDepts]
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

INSERT dbo.YesDepts_History
 (        [YesDeptID]  ,
    [DepartmentName_old]  ,
    [DepartmentName_new]  ,
    [BUN_old] ,
    [BUN_new],
    [ADName_old],
    [ADName_new],
    [ServiceNowName_old]  ,
    [ServiceNowName_new]  ,    
    [EffectiveDate_old]  ,
    [EffectiveDate_new]  ,
    [ExpirationDate_old]  ,
    [ExpirationDate_new]  ,
    [CreatedDate_old] ,
    [CreatedDate_new] ,
    [ModifiedDate_old] ,
    [ModifiedDate_new] ,
    [Active_old],
    [Active_new],  
    [Changed_By],
    [Changed_At],
    [Change_Type])
SELECT
 ISNULL(i.YesDeptID, d.YesDeptID),
    d.[DepartmentName],
    i.[DepartmentName],
    d.[BUN],
    i.[BUN],
    d.[ADName],
    i.[ADName],
    d.[ServiceNowName],
    i.[ServiceNowName],    
    d.[EffectiveDate],
    i.[EffectiveDate],
    d.[ExpirationDate],
    i.[ExpirationDate],
    d.[CreatedDate],
    i.[CreatedDate],
    d.[ModifiedDate],
    i.[ModifiedDate],
    d.[Active],
    i.[Active],
    SUSER_NAME(),
    GETDATE(),
    CASE
        WHEN NOT EXISTS (SELECT * FROM Inserted) THEN 'DELETED'
        WHEN NOT EXISTS (SELECT * FROM Deleted) THEN 'INSERTED'
        ELSE 'UPDATED'
    END
FROM inserted i
FULL JOIN deleted d ON d.YesDeptID = i.YesDeptID;
GO

 

--    DISABLE TRIGGER!!!!!!!!!!!!!!!!!!!!!!
ALTER TABLE [dbo].[YesDepts] DISABLE TRIGGER [trg_YesDeptsHistory]
GO


--    repopulate YesDepts from o_YesDepts
SET IDENTITY_INSERT dbo.YesDepts ON;
GO

INSERT INTO [dbo].[YesDepts]
          ([YesDeptID]
           ,[DepartmentName]
          ,[BUN]
           ,[ADName]
          ,[ServiceNowName]
          ,[EffectiveDate]
          ,[ExpirationDate]
          ,[CreatedDate]
          ,[ModifiedDate]
          ,[Active])
SELECT [YesDeptID]
     ,[DepartmentName]
     ,[BUN]
      ,NULL AS [ADname]
     ,[ServiceNowName]
     ,[EffectiveDate]
     ,[ExpirationDate]
     ,[CreatedDate]
     ,[ModifiedDate]
     ,[Active]
 FROM [dbo].[o_YesDepts]
GO

SET IDENTITY_INSERT dbo.YesDepts OFF;
GO


SET IDENTITY_INSERT dbo.YesDepts_History ON;
GO

INSERT INTO [dbo].[YesDepts_History]
          ([YesDeptsHistoryID]
           ,[YesDeptID]
          ,[DepartmentName_old]
          ,[DepartmentName_new]
          ,[BUN_old]
          ,[BUN_new]
           ,[ADname_old]
           ,[ADname_new]
          ,[ServiceNowName_old]
          ,[ServiceNowName_new]
          ,[EffectiveDate_old]
          ,[EffectiveDate_new]
          ,[ExpirationDate_old]
          ,[ExpirationDate_new]
          ,[CreatedDate_old]
          ,[CreatedDate_new]
          ,[ModifiedDate_old]
          ,[ModifiedDate_new]
          ,[Active_old]
          ,[Active_new]
          ,[Changed_By]
          ,[Changed_At]
          ,[Change_Type])
SELECT [YesDeptsHistoryID]
     ,[YesDeptID]
     ,[DepartmentName_old]
     ,[DepartmentName_new]
     ,[BUN_old]
     ,[BUN_new]
      ,NULL AS [ADname_old]
      ,NULL AS [ADname_new]
     ,[ServiceNowName_old]
     ,[ServiceNowName_new]
     ,[EffectiveDate_old]
     ,[EffectiveDate_new]
     ,[ExpirationDate_old]
     ,[ExpirationDate_new]
     ,[CreatedDate_old]
     ,[CreatedDate_new]
     ,[ModifiedDate_old]
     ,[ModifiedDate_new]
     ,[Active_old]
     ,[Active_new]
     ,[Changed_By]
     ,[Changed_At]
     ,[Change_Type]
 FROM [dbo].[YesDepts_History]


SET IDENTITY_INSERT dbo.YesDepts_History OFF;
GO

 

--    reenable trigger
ALTER TABLE [dbo].[YesDepts] ENABLE TRIGGER [trg_YesDeptsHistory]
GO

 

--    RECREATE FKs, renaming a couple

ALTER TABLE [dbo].[UserAccessLevels]  WITH CHECK 
ADD  CONSTRAINT [FK_UserAccessLevels_DeptID_YesDepts] FOREIGN KEY([DeptID])
REFERENCES [dbo].[YesDepts] ([YesDeptID])
GO

ALTER TABLE [dbo].[UserAccessLevels] CHECK CONSTRAINT [FK_UserAccessLevels_DeptID_YesDepts]
GO


ALTER TABLE [dbo].[UserTempAccessLevels]  WITH CHECK 
ADD  CONSTRAINT [FK_UserTempAccessLevels_DeptID_YesDepts] FOREIGN KEY([DeptID])
REFERENCES [dbo].[YesDepts] ([YesDeptID])
GO

ALTER TABLE [dbo].[UserTempAccessLevels] CHECK CONSTRAINT [FK_UserTempAccessLevels_DeptID_YesDepts]
GO


ALTER TABLE [dbo].[UserTempAccessLevels_og]  WITH CHECK 
ADD  CONSTRAINT [FK_UserTempAccessLevels_og_DeptID_YesDepts] FOREIGN KEY([DeptID])
REFERENCES [dbo].[YesDepts] ([YesDeptID])
GO

ALTER TABLE [dbo].[UserTempAccessLevels_og] CHECK CONSTRAINT [FK_UserTempAccessLevels_og_DeptID_YesDepts]
GO


--    CHECK transfer 
select count(*) YesDepts
from YesDepts;

select count(*) o_YesDepts
FROM o_YesDepts;

select count(*) YesDepts_History
from YesDepts_History;

select count(*) o_YesDepts_History
from o_YesDepts_History;


--    CHECK trigger INSERT, UPDATE, DELETE

INSERT INTO [dbo].[YesDepts]
          ([DepartmentName]
          ,[BUN]
           ,[ADName]
          ,[ServiceNowName]
          ,[EffectiveDate]
          ,[ExpirationDate]
          ,[CreatedDate]
          ,[ModifiedDate]
          ,[Active])
    VALUES
          ('Crazyville'
          ,'0706'
          ,'Chicken Lips'
          ,'KFC'
          ,getdate()+2
          ,getdate()+200
           ,getdate()
          ,getdate()
          ,1)
GO


--    CHECK insert, grab YesDeptID of TOP ROW
select *
from YesDepts_History
order by changed_at desc


--    CHECK update
update YesDepts
set DepartmentName = 'Janitor',
ADName = 'Jerry was a Race Car Driver'
where YesDeptID = 70


--    CHECK delete
delete YesDepts
where YesDeptID = 70


--    cleanup
DROP TABLE IF EXISTS o_YesDepts;
GO

DROP TABLE IF EXISTS o_YesDepts_History;
GO

TRUNCATE TABLE YesDepts_History;

Seth Buxton
0

Comments

1 comment

Sort by
  • Christian Perez
    Official comment

    Hi Seth,

    Thanks for reaching out to Redgate Support, sorry to hear you are having a little trouble with your SQL Compare install in this case. I suspect what may be happening is that since we are adding a column to the parent (YesDepts) table that has a trigger-maintained history table (YesDepts_History), we may be running into a dependency chain issue.

    I suspect SQL Compare may be tryting to drop and recreate the trigger because it references the new column, but the history table doesnt have the new columns yet or vice versa, leading to data loss.

    To start remediating this, I would first try using some of the Project/Comparison Options designed around triggers and see if if we get the wanted behavior. Have you already tried any of the below options to see if we can stop the trigger from being dropped and recreated?

    • Project Options > Ignore Tab > Consider Ignoring Triggers for initial comparison 
    • Enable “Disable DDL triggeres during deployment”
    • Enable “Include transactions”
      • This lets you rollback if a deployment fails midway through execution.
    • Options > Behavior > Use “Ignore trigger order” in case execution order is causing the problem
    • Options > Dependencies > Set :Include dependencies" to include triggers, history tables, parent tables are all considered together.

    Besides the above, other options include manually disabling the trigger at the beginning of the deployment script, making the schema changes, reenabling the trigger at the end. You could also consider using temporal tables instead of trigger-maintained history to eliminate the need for trigger maintenance and SQL Compare handles them naturally.

     

    Christian Perez

Add comment

Please sign in to leave a comment.