SQL Server Transaction

Question

I have 2 sql server statements .

Delete from hello where id=1

Insert into hello name,age 
select name ,age from welcome

If either of one fails.No Deletion or Insertion should be done.

I tried with transaction

Begin Tran

    Delete from hello where id=1

    Insert into hello (name,age) 
    select name ,age from welcome


Commit Tran

But if either one is gone wrong .Other one is committed.Am i missing something.

2 delete statements

BEGIN TRY
    BEGIN TRAN;


  delete from hello where id=1

  delete from hello where id=19  // here id=19 doesn't exist

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

Here id=19 doesn't exists so it should rollback but it is deleting id=1. It is committed instead of rollback.What should i do in this Scenerio..

Answer 1

But if either one is gone wrong .Other one is committed.Am i missing something.

Yes, you are missing error handling. Depending on the error, T-SQL batches may continue following an error and execute subsequent statements, including the commit. Below is a structured error handling example to avoid this.

BEGIN TRY
    BEGIN TRAN;

    DELETE FROM hello WHERE id=1;

    INSERT INTO hello (name,age) 
    SELECT name ,age FROM welcome;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

UPDATE:

If you have additional business rules that are not SQL Server errors, you can detect the condition in the T-SQL code and raise an error to invoke the CATCH block and rollback:

BEGIN TRY
    BEGIN TRAN;

    DELETE FROM hello WHERE id=1;

    IF @@ROWCOUNT < 1
    BEGIN
        RAISERROR('Row not found', 16, 1);
    END;

    INSERT INTO hello (name,age) 
    SELECT name ,age FROM welcome;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;