I am not sure if this is what you and/or the OP had in mind but give it a try.

CREATE TABLE #usTab1

(

Col1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

Col2 Tinyint DEFAULT (0) NOT NULL,

Col3 NVARCHAR(30) NOT NULL

);

insert #usTab1

select 0, 'Allows change'

union all

select 1, 'Can''t change'

;

select * from #usTab1;

ALTER TABLE #usTab1 WITH NOCHECK

ADD CONSTRAINT my_chk CHECK (Col2=0 AND Col3=Col3) ;

GO

UPDATE #usTab1

SET Col3 = 'works'

WHERE Col1 = 1;

GO

UPDATE #usTab1

SET Col3 = 'A bust'

WHERE Col1 = 2;

GO

BEGIN TRANSACTION T1;

ALTER TABLE #usTab1

DROP CONSTRAINT my_chk;

insert #usTab1 select 0, 'Allows change';

insert #usTab1 select 1, 'Can''t change';

ALTER TABLE #usTab1 WITH NOCHECK

ADD CONSTRAINT my_chk CHECK (Col2=0 AND Col3=Col3) ;

COMMIT TRANSACTION T1;

select * from #usTab1;

GO

DROP TABLE #usTab1;

I'd need to check whether the scope of the TRANSACTION covers the ALTER TABLE statements but I think it does.

Also, this won't work if you're doing a MERGE that UPDATEs and INSERTs.