Using rollback - , PL-SQL Programming

Using ROLLBACK

The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if you made a mistake such as deleting the wrong row from the table, rollbacks restore the original data. Secondly, if you start the transaction that you cannot finish as an exception is raised or the SQL statement fails the rollback return to the starting point to take the correct action and possibly try again.

Consider the illustration shown below, in which you insert information about an employee into 3 various database tables. All 3 tables have a column that hold employee numbers and is constrained by an exclusive index. If an INSERT statement tries to store a duplicate employee number, the predefined exception DUP_VAL_ON_ INDEX is raised. In that situation, you want to undo all the changes, so you issue a rollback in an exception handler.

DECLARE

emp_id INTEGER;

...

BEGIN

SELECT empno, ... INTO emp_id, ... FROM new_emp WHERE ...

...

INSERT INTO emp VALUES (emp_id, ...);

INSERT INTO tax VALUES (emp_id, ...);

INSERT INTO pay VALUES (emp_id, ...);

...

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK;

...

END;

Posted Date: 10/4/2012 5:14:24 AM | Location : United States







Related Discussions:- Using rollback - , Assignment Help, Ask Question on Using rollback - , Get Answer, Expert's Help, Using rollback - Discussions

Write discussion on Using rollback -
Your posts are moderated
Related Questions
Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

Data Types in SQL SQL's concept does not differ significantly from that defined in the theory book, apart from that business concerning NULL. However, the theory book equates

UNNEST operator in SQL The inverse operator of GROUP is UNGROUP. SQL has an operator, UNNEST, that can be used for similar purposes, but its method of invocation is somewhat p

THEORY OF SPONTANEOUS GENERATION - ABIOGENESIS OR AUTOGENESIS - According to this theory, the existing living communities have originated from non-living organic matter with

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

Project Description: I want to write some SQL statements. The things I need are between pages 5-7. The only problem is that i want it till tomorrow. Skills required is SQL

COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.