Can you modify SQL DB schema in a transaction to know if all changes were applied?
- by Chris F
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
-- maybe COMMIT here?
INSERT INTO SchemaChangeLog VALUES(...)
COMMIT TRANSACTION
