要求不要使用事务并使用替代方法来模拟事务

 CREATE SCHEMA someschema;
GO


CREATE TABLE someschema.tableA
(id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, 
 ColA VARCHAR(10) NOT NULL
);
GO

CREATE TABLE someschema.tableB
(id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, 
 ColB VARCHAR(10) NOT NULL
); 
GO


CREATE PROCEDURE someschema.ProcedureName @ColA VARCHAR(10), 
                                          @ColB VARCHAR(10)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO someschema.tableA(ColA)
    VALUES(@ColA);

    INSERT INTO someschema.tableB(ColB)
    VALUES(@ColB);

--Implement error
    SELECT 1/0 

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@trancount > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END;
    THROW;
    RETURN;
END CATCH;
END;
GO

 

 GO



CREATE PROCEDURE someschema.ProcedureNameNoTransaction @ColA VARCHAR(10), 
                                                       @ColB VARCHAR(10)
AS
SET NOCOUNT ON;
BEGIN
BEGIN TRY
    DECLARE @tableAid INT;
    DECLARE @tableBid INT;

    INSERT INTO someschema.tableA(ColA)
    VALUES(@ColA);
    SET @tableAid = SCOPE_IDENTITY();

    INSERT INTO someschema.tableB(ColB)
    VALUES(@ColB);
    SET @tableBid = SCOPE_IDENTITY();

--Implement error
    SELECT 1/0 

END TRY
BEGIN CATCH
    DELETE FROM someschema.tableA
    WHERE id = @tableAid;

    DELETE FROM someschema.tableB
    WHERE id = @tableBid;

    THROW;

    RETURN;
END CATCH;
END;
GO