alter table missing @@error check after it results in changes with no transaction
fpdave100
Posts: 12 Bronze 1
in SQL Compare
SQL Compare has generated the following:
@ERROR... after the first ALTER TABLE...
if this statement fails (due to bad data that breaks the constraint, or if it already exists) then the 2nd alter table is run without NO EXEC, and if it is successful then we don't find out that an error occurred.
Its worse than that, because of the use of XACT ABORT, the first line causes an error, and the transaction is rolled back, but all subsequent lines are done OUTSIDE of a transaction!!
This is very bad indeed!
PRINT N'Adding foreign keys to [dbo].[Entity_PhoneNumber]' GO ALTER TABLE [dbo].[Entity_PhoneNumber] ADD CONSTRAINT [FK_Entity_PhoneNumber_Entity] FOREIGN KEY ([EntityID]) REFERENCES [dbo].[Entity] ([ID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Entity_PhoneNumber] ADD CONSTRAINT [FK_Entity_PhoneNumber_PhoneNumber] FOREIGN KEY ([PhoneNumberID]) REFERENCES [dbo].[PhoneNumber] ([ID]) ON DELETE CASCADE GO IF @@ERROR <> 0 SET NOEXEC ON GO
@ERROR... after the first ALTER TABLE...
if this statement fails (due to bad data that breaks the constraint, or if it already exists) then the 2nd alter table is run without NO EXEC, and if it is successful then we don't find out that an error occurred.
Its worse than that, because of the use of XACT ABORT, the first line causes an error, and the transaction is rolled back, but all subsequent lines are done OUTSIDE of a transaction!!
This is very bad indeed!
