Using a transaction to insert a record after updating the same table?-CodePudding

I'm using raw SQL and trying to do an update and then an insert on the same table, do you think I need some type of transaction or something?

If I add an INSERT and then an UPDATE in the same script file, will the order of the execution be respected by SQL?

Ended up with this so far:

IF NOT EXISTS (SELECT TOP 1 * FROM dbo.Settings 
               WHERE Descr = 'GL for Credit Memos')
BEGIN
    SET IDENTITY_INSERT dbo.Settings ON

    DECLARE @newOrderNo INT;
    SET @newOrderNo = 7 -- OrderNo for credit memo to display below 'GL for A/P'

    -- Update order no. for settings below 'GL for Credit Memo'
    UPDATE dbo.Settings 
    SET [OrderNo] = [OrderNo]   1 
    WHERE [OrderNo] >= @newOrderNo

    INSERT INTO [dbo].[Settings]
               ([SettingID],
               [Created],
               [Descr],
               [Category],
               [OrderNo],
               [DataType],
               [InActive],
               [IsRequired])
         VALUES
               (198, -- New Enum value for setting ID for 'Gl for Credit Memo'
               DEFAULT,
               'GL for Credit Memo',
               'Accounting',
               @newOrderNo,
               4,
               NULL,
               1)
    
    SET IDENTITY_INSERT dbo.Settings OFF
END

CodePudding user response：

In SQL Server, autocommit transactions are turned on by default during installation. That means that for most servers, each individual SQL statement opens a transaction in the background, executes, and then commits the transaction if the statement succeeds or rolls it back if the statement fails, all behind the scenes.

You can override that behavior by issuing a BEGIN TRANSACTION statement, which will open a transaction and keep it open until you issue an explicit COMMIT TRANSACTION or ROLLBACK statement. Usually the ROLLBACK will live in the CATCH clause of a TRY...CATCH. Everything between those explicit statements will be treated as a single transaction.

In your case, the UPDATE will be in one transaction and the INSERT will be in another.

Here's the Microsoft documentation.

CodePudding user response：

Whole statements in SQL are always executed in order. There is no reordering except within a single statement (for example the order that rows may be updated in a single UPDATE is undefined).

You probably do want a transaction here, if you want to ensure that the code works in an "all-or-nothing" fashion.

But you do not need any complex catch/rolback code. Contrary to popular opinion, it is almost never necessary, and usually actively harmful.

The only thing you need to do, and this you must do to ensure proper rollbacks, is to use XACT_ABORT. This tells the server to ensure a rollback will always happen, regardless of any errors.

SET XACT_ABORT ON;

BEGIN TRANSACTION;

-- your code here

COMMIT;

If you are executing in SSMS then you will see any errors. If executing from a client app or script, you can catch the error in the client app, then show the error to the user and/or log it.