T-SQL vs PL/SQL simple question

  • 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.

  • 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 🙂

  • 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

  • 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

  • Thank you for clearifying this, ARUN SAS.

    Obsc.

  • 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

  • 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