We ran into a problem at work where we have a table, along with a history table, or some other form of almost identical table where the definitions need to be in sync.
Here’s the scenario:
We have a table, say “clients”. There is a requirement that we track any changes to this table in a “clients_history” table. This could be implemented using a trigger, or just a secondary save in your repository using EF. The implementation is irrelevant.
A few months later, a developer is working a user story where they need to add a field to clients to track if the company doesn’t want sales people to call on the client any longer.
The developer adds a field to the clients table:
ALTER TABLE dbo.clients ADD
isdeadbeat bit NULL
Now there are several things that will happen:
- The developer will not know or remember to add this field to the client_history table
- Depending on how the trigger is coded, it could raise a difficult to troubleshoot error trying to insert into the clients_history table
- The new field data will not be saved in the clients_history table
How can we resolve this issue. Well, there are dozens of ways, but what’s the easiest?
If you deploy your Database changes from a TFS project, you can add a post deployment script that runs after every deployment. You probably do this already for inserting values into lookup tables etc.
We’ll add a new script and insert the following code:
DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM (
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.clients_history')
EXCEPT
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.clients')
) AS A
IF (@Count > 0)
BEGIN
RAISERROR('There is a mismatch between two tables',18,-1);
RETURN
END
GO
When we run just the SELECT statements with the EXCEPT, we see the there are differences identified:
If we run the entire script, it will raise and exception to let the person doing the deployment know that there is an issue that needs to be addressed. You’ll just add a new statement for each table pair that you want to check, as they are identified.
Now call this new script with your Script.PostDeployment.sql file.
Now whenever someone publishes this script, if there are unwanted differences between the two files, they will be noted.
NOTES:
- The table that is most likely to have changes that need to be sync’d, in this case, the clients table, should be first in the EXCEPT statement.
- If there is a field that you want to ignore for some reason, you must provide a where clause.