sql-server - 如果条目是通过在多个表中输入数据的存储过程进行的，如何从表中恢复数据

Question

如果通过在多个表中输入数据的存储过程进行输入，但在一个表中输入数据时出现错误，如何从表中恢复数据。所以现在我也必须从其他表中恢复数据。怎么做？

标签： sql-serverstored-procedures

Answer 1

使用显式事务和TRY...CATCH. 这是一个基本示例，但演示了这个想法：

--Create Sampel tables

CREATE TABLE dbo.SampleTable1 (ID int IDENTITY PRIMARY KEY,
                               SomeString varchar(20));
GO
CREATE TABLE dbo.SampleTable2 (ID int IDENTITY PRIMARY KEY,
                               fID int,
                               SomeInt int);

ALTER TABLE dbo.SampleTable2 ADD CONSTRAINT FK_Sample2Sample1 FOREIGN KEY (fID) REFERENCES dbo.SampleTable1 (ID);

GO
--Workign sample
BEGIN TRY
    BEGIN TRANSACTION YourTransaction;

    INSERT INTO dbo.SampleTable1 (SomeString)
    VALUES('abc123');

    INSERT INTO dbo.SampleTable2 (fID, SomeInt)
    VALUES(1,2);

    COMMIT TRANSACTION YourTransaction;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION YourTransaction;

    THROW;
END CATCH;    

GO
--Failing example
BEGIN TRY
    BEGIN TRANSACTION YourTransaction;

    INSERT INTO dbo.SampleTable1 (SomeString)
    VALUES('def456');

    INSERT INTO dbo.SampleTable2 (fID, SomeInt)
    VALUES(3,2); --Invalid foreign key

    COMMIT TRANSACTION YourTransaction;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION YourTransaction;

    THROW;
END CATCH;  

GO

SELECT * --Notice ID 2 missing.
FROM dbo.SampleTable1;

GO
DROP TABLE dbo.SampleTable2;
DROP TABLE dbo.SampleTable1;