In a previous article I walked through creating a temporal table in SQL Server, which gives you the ability to track all changes without resorting to triggers or custom code.
When you have a temporal table, it's an extra couple of steps to alter it - generally adding new columns.
First, disable versioning on the table:
ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = OFF); GO
Next, apply your change to both the regular and history table:
ALTER TABLE dbo.MyTable_Hist ADD SomeNewColumn VARCHAR(10) NULL; GO ALTER TABLE dbo.MyTable ADD SomeNewColumn VARCHAR(10) NULL; GO
Finally, re-enable versioning:
ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.MyTable_Hist, DATA_CONSISTENCY_CHECK=ON); GO
If you're using a database project in Visual Studio, these scripts will be written for you - all you need is to add the column to both tables, and generate a publish script.