Can you modify SQL DB schema in a transaction to know if all changes were applied?

Question

As part of my (new) database version control methodology, I'm writing a "change script" and want the change script to insert a new row into the SchemaChangeLog table if the script is executed successfully, or to reverse changes if any single change in the script fails.

Is it possible to do schema changes in a transaction and only if it gets committed to then do the INSERT?

For example (psuedo-code, I'm not too good with SQL):

SET XACT_ABORT ON
BEGIN TRANSACTION
PRINT 'Add Col2 to Table1'
IF NOT EXIST (SELECT * FROM sys.columns WHERE NAME='Col2' AND object_id=OBJECT_ID('Table1'))
BEGIN
    ALTER TABLE [dbo].[Table1]
    ADD Col2 int NULL
END
INSERT INTO SchemaChangeLog(MajorVer, MinorVer, PointVer, ScriptName, AppliedDate) VALUES(N'01', N'01', N'0000', N'update.01.01.0000.sql', GETDATE())
COMMIT TRANSACTION

Answer 1

If you want two actions to be atomic, embed them in a transaction. So your two actions are 1) ALTER TABLE and 2) INSERT INTO SchemaChangeLog. Therefore, your BEGIN TRANSACTION must occur before the ALTER TABLE, and the COMMIT after the INSERT.

As a side note, there is is an already built-in mechanism into SQL Server for tracking schema changes, it works out of the box and the best is that it will catch everybody's changes, not only yours: Event Notifications.