Hello, i am unable to understand the Atomicity in Acid Properties, when i am executing a transaction consisting of different statements in which one of the update contains column which is not present. the transation ran success for all the other except the column which is not present.and all the other statements updated the table. here i got confusion as **Database modifications must follow an all or nothing rule** but here the remaining statement in the same transaction are success. can any one please help me to understand the atomicity with an example please.
If you run your statements within a transaction, you also need to use some sort of error handlign, preferrably with TRY/CATCH, and in the CATCH-block you need to run a ROLLBACK in order to maintain atomicity of the transaction. Otherwise, SQL Server will run each statement. When a statement fails, it will move over to the next statement, and finally handle your COMMIT-statement, which will commit all the successful changes within your transaction. Conceptually it will something like this BEGIN TRY BEGIN TRAN; --statement 1 --statement 2 --statement 3 COMMIT; END TRY BEGIN CATCH ROLLBACK; --handle errors (throw-statements, logging of errors etc) END CATCH