Question

我有以下T-SQL:

USE [MYDB]
GO
SET XACT_ABORT, NOCOUNT ON
GO
BEGIN TRANSACTION;
BEGIN TRY
        ALTER TABLE [dbo].[ContactRole] 
        DROP CONSTRAINT [FK_8bff7074914bc29885004c0a323] 
        GO
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

如果说:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near FK_8bff7074914bc29885004c0a323 .

单凭交易,或者在交易中,不存在差错。

Answer 1

<代码>>/代码>后 ALTER TABLE ... DROPCONTRAINT . line.

....
BEGIN TRANSACTION;
BEGIN TRY
    ALTER TABLE [dbo].[ContactRole] 
    DROP CONSTRAINT [FK_8bff7074914bc29885004c0a323] 

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

GO is not a SQL keyword - it s only understood by SQL Server Management Studio as a "batch separator".

另外,在<代码>后插入声明。 ALTER TABLE的指挥——毕竟,如果情况不正确的话,则守则的执行将跳跃到 catch的边缘,因此,只有在一切照旧的情况下才能执行。

Answer 2

you shouldn t have the Go because it indicates the end of the batch and your commit should be right after the alter table.

Also there is no need to check if a transaction is running on the catch because on this situation, it will always have

USE [MYDB]
GO
SET XACT_ABORT, NOCOUNT ON
GO
BEGIN TRANSACTION;
BEGIN TRY
        ALTER TABLE [dbo].[ContactRole] 
        DROP CONSTRAINT [FK_8bff7074914bc29885004c0a323] 

        COMMIT TRANSACTION; 
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;

        ROLLBACK TRANSACTION;
END CATCH

友情链接