CODEWITHSUNDEEP
sql-serverstored-procedures
Praneeth Podduturi
Praneeth Podduturi

Reputation: 5

how to revert back data from table if entry is made by stored procedure which enters data in multiple tables

how to revert back data from table if entry is made by stored procedure which enters data in multiple tables, but there is a error in data entry in one table. So now i have to revert back data from other tables too. How to do that?

Upvotes: 0

Views: 299

Answers (1)

Thom A
Thom A

Reputation: 95579

Use explicit transactions, and a TRY...CATCH. This is an basic example, but demonstrates the idea:

--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;

Upvotes: 2

Related Questions