In this example, you'll insert a number of rows into the Student table as part of a transaction, and after each insert, you'll check whether an error occurred. If it did, you'll roll back the transaction. If all the inserts succeed, you commit the transaction. In both cases, you display a message saying whether the transaction was committed or rolled back:

BEGIN TRANSACTION MyTransaction

INSERT INTO Student (StudentID, Name) VALUES (101, 'Dave')
IF @@ERROR != 0
BEGIN
   ROLLBACK TRANSACTION MyTransaction
   PRINT 'Cannot insert Dave! Transaction rolled back.'
   RETURN
END
INSERT INTO Student (StudentID, Name) VALUES (102, 'Claire')
IF @@ERROR != 0
BEGIN
   ROLLBACK TRANSACTION MyTransaction
   PRINT 'Cannot insert Claire! Transaction rolled back.'
   RETURN
END

INSERT INTO Student (StudentID, Name) VALUES (103, 'Anne')
IF @@ERROR != 0
BEGIN
   ROLLBACK TRANSACTION MyTransaction
   PRINT 'Cannot insert Anne! Transaction rolled back.'
   RETURN
END

COMMIT TRANSACTION MyTransaction
IF @@ERROR != 0
   PRINT 'Could not COMMIT transaction'
ELSE
   PRINT 'Transaction committed.'

This example looks a bit different from the previous one. The batch of statements tries to insert three records to the Student table. If any of the INSERT statements fail, you roll back the transaction, display a message on the screen, and stop the execution.

You achieve this by verifying the @@ERROR system variable after each statement that could generate an error:

INSERT INTO Student (StudentID, Name) VALUES (101, 'Dave')
IF @@ERROR != 0
BEGIN
   ROLLBACK TRANSACTION MyTransaction
   PRINT 'Cannot insert Dave! Transaction rolled back.'
   RETURN
END

The @@ERROR system variable is automatically updated after each SQL query, so it is necessary to test it each time you do something to the database. Another important thing to note is that you call RETURN after rolling back the transaction. This wouldn't be necessary if you had rolled back to a savepoint, but if you roll back the entire transaction, the execution should stop; otherwise, the following SQL statements would mistakenly assume that they're part of a transaction and call ROLLBACK or COMMIT at certain points.

You can also test the outcome of the COMMIT command. This way you deal with the case where there's a problem and the transaction can't be committed. The most likely reason for this is if you already finalized the transaction (say, rolled back the transaction when an INSERT failed without calling RETURN after ROLLBACK). This is a great debugging technique because it provides feedback about the transaction's output, but once you're confident that the SQL script is well constructed, you can omit this:

COMMIT TRANSACTION MyTransaction
IF @@ERROR != 0
   PRINT 'Could not COMMIT transaction'
ELSE
   PRINT 'Transaction committed.'

The first time the batch is executed, the output should read as follows:

   Transaction committed.

However, if you now execute the batch again, the transaction will be rolled back because you can't insert two rows with the same primary key value into a table:

   Violation of PRIMARY KEY constraint PK__Student__59063A47'.

   Cannot insert duplicate key in object 'Student'.
   The statement has been terminated.
   Cannot insert Dave! Transaction rolled back.

In this example, you'll try to insert three rows into the Student table. One of the inserts will be rolled back using a savepoint. If any exceptions are raised in the process, you roll back the whole transaction and bring the Student table back to its original state.

Here's the piece of code that does this:

BEGIN

   INSERT INTO Student (StudentID, Name) VALUES (101, 'Dave');
   INSERT INTO Student (StudentID, Name) VALUES (102, 'Claire');

   SAVEPOINT BeforeAddingAnne;
   INSERT INTO Student (StudentID, Name) VALUES (103, 'Anne');
   ROLLBACK TO BeforeAddingAnne;

   COMMIT;

EXCEPTION
   WHEN OTHERS
      THEN ROLLBACK;
END;
/

After typing the batch, you can save it using SAVE:

SAVE exception

Once the procedure is saved to a file, you can call it like this:

@exception

If any of the SQL queries in the BEGIN block generates an error, the execution is passed to the EXCEPTION block:

EXCEPTION
   WHEN OTHERS
      THEN ROLLBACK;

You can handle separately many different kinds of predefined exceptions, and you can also define and raise your own exceptions. Also, if you have nested BEGIN blocks or stored procedures, the unhandled exceptions propagate vertically. In this case, you have used the WHEN OTHERS option, which handles all exceptions. There you use ROLLBACK to roll back the transaction. You can learn more about Oracle exception handling in Chapter 9.

The procedure you wrote would end up adding two rows into the Student table, as long as no errors are generated inside the script. If an error does occur, everything is brought back to the original state. If, for example, you already have a student with a StudentID of 102 before executing the script, an error will be generated when trying to add Claire, and the transaction is rolled back (so not even Dave will end up being in the Student table).

After you've run this code once, each subsequent time you execute @exception, the transaction will be rolled back.