May 28, 2009 at 2:26 am
Hi, I'm used to Oracle's way of doing things. And one of my favorites are the commit/rollback features, are there any easy equivalents in T-SQL, without the need for building a transaction first?
Obsc.
May 28, 2009 at 3:20 am
Since I have no idea about Oracle. Could you please explain the "commit/rollback features"? This makes it easier for T-SQL guys and gals 🙂
May 28, 2009 at 3:33 am
Obscurr (5/28/2009)
without the need for building a transaction first?Obsc.
Hi,
In Microsoft SQL Server, transactions are explicit by definition. This implies that an individual SQL statement is not part of a logical transaction by default. A SQL statement belongs to a logical transaction if the
transaction explicitly initiated by the user with a BEGIN TRANSACTION (or BEGIN TRAN) statement is still in effect. The logical transaction ends with a corresponding COMMIT TRANSACTION (or COMMIT TRAN) or ROLLBACK TRANSACTION (or
ROLLBACK TRAN) statement. Each SQL statement that is not part of a logical transaction is committed on completion.
BEGIN TRAN
/*SOME SELECT/UPDATE/DELETE*/
IF @@ROWCOUNT0/*IF SUCCESS*/
COMMIT TRAN
ELSE
ROLLBACK TRAN
In Oracle, transactions are implicit as set by the ANSI standard. The implicit transaction model requires that each SQL statement is part of a logical transaction. A new logical transaction is automatically initiated when a COMMIT or ROLLBACK command is executed. This also implies that data changes from an individual SQL
statement are not committed to the database after execution. The changes are committed to the database only when a COMMIT statement is run. The differences in the transaction models impact the coding of application procedures.
/*BEGIN TRAN -- statement ignored ---*/
/*SOME SELECT/UPDATE/DELETE*/
IF @@ROWCOUNT0/*IF SUCCESS*/
COMMIT TRAN
ELSE
ROLLBACK TRAN
ARUN SAS
May 28, 2009 at 3:34 am
Rollbacks are nice to have if you've done an error, ex deleted something without the where clause. just issue a rollback command, and the previous deletion is rolled back. What do you do if you've issued a wrong command in t-sql? Is there a way of undoing the command just issued, or do I have to wrap the code into a transaction?
Oracle:
delete from emp;
rollback; /* undo the changes */
Obsc
May 28, 2009 at 3:42 am
Thank you for clearifying this, ARUN SAS.
Obsc.
May 28, 2009 at 3:46 am
hi,
Yes, in the PSql its possible,
but in the Tsql,we should do the COMMIT/ROLLBACK tran with the begin tran first.
ARUN SAS
May 28, 2009 at 9:46 am
Lookup the topic SET IMPLICIT_TRANSACTIONS in Books Online. You can set this for the connections, or set it for all new connections in SSMS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply