ch11.htm

Learn SQL in an easy way...

syntax for creating a savepoint using Oracle SQL is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">SAVEPOINT savepoint_name;
</FONT></PRE>
<P>Sybase SQL Server's syntax to create a savepoint is the following:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">save transaction savepoint_name
</FONT></PRE>
<P>This following example uses Oracle SQL syntax.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;</B>
SQL&gt; <B>UPDATE BALANCES SET CURR_BAL = 25000 WHERE ACCOUNT_ID = 5;</B>
SQL&gt; <B>SAVEPOINT save_it;</B>
SQL&gt;<B> DELETE FROM BALANCES WHERE ACCOUNT_ID = 5;
</B>SQL&gt; <B>ROLLBACK TO SAVEPOINT save_it;</B>
SQL&gt; <B>COMMIT;</B>
SQL&gt; <B>SELECT * FROM BALANCES;</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">The BALANCES table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Average_Bal</B></TD>
		<TD ALIGN="LEFT"><B>Curr_Bal</B></TD>
		<TD ALIGN="LEFT"><B>Account_ID</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1298.53</TD>
		<TD ALIGN="LEFT">854.22</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">5427.22</TD>
		<TD ALIGN="LEFT">6015.96</TD>
		<TD ALIGN="LEFT">2</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">211.25</TD>
		<TD ALIGN="LEFT">190.01</TD>
		<TD ALIGN="LEFT">3</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">73.79</TD>
		<TD ALIGN="LEFT">25.87</TD>
		<TD ALIGN="LEFT">4</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1285.90</TD>
		<TD ALIGN="LEFT">25000.00</TD>
		<TD ALIGN="LEFT">5</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1234.56</TD>
		<TD ALIGN="LEFT">1543.67</TD>
		<TD ALIGN="LEFT">6</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">345.25</TD>
		<TD ALIGN="LEFT">348.03</TD>
		<TD ALIGN="LEFT">7</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1250.76</TD>
		<TD ALIGN="LEFT">1431.26</TD>
		<TD ALIGN="LEFT">8</TD>
	</TR>
</TABLE>
</P>
<P>This example uses Sybase SQL syntax:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt;<B> begin transaction</B>
2&gt; <B>update BALANCES set Curr_Bal = 25000 where Account_ID = 5</B>
3&gt; <B>save transaction save_it</B>
4&gt; <B>delete from BALANCES where Account_ID = 5</B>
5&gt; <B>rollback transaction save_it</B>
6&gt; <B>commit transaction</B>
7&gt; <B>go</B>
1&gt; <B>select * from BALANCES</B>
2&gt; <B>go</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">The BALANCES table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Average_Bal</B></TD>
		<TD ALIGN="LEFT"><B>Curr_Bal</B></TD>
		<TD ALIGN="LEFT"><B>Account_ID</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1298.53</TD>
		<TD ALIGN="LEFT">854.22</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">5427.22</TD>
		<TD ALIGN="LEFT">6015.96</TD>
		<TD ALIGN="LEFT">2</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">211.25</TD>
		<TD ALIGN="LEFT">190.01</TD>
		<TD ALIGN="LEFT">3</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">73.79</TD>
		<TD ALIGN="LEFT">25.87</TD>
		<TD ALIGN="LEFT">4</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1285.90</TD>
		<TD ALIGN="LEFT">25000.00</TD>
		<TD ALIGN="LEFT">5</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1234.56</TD>
		<TD ALIGN="LEFT">1543.67</TD>
		<TD ALIGN="LEFT">6</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">345.25</TD>
		<TD ALIGN="LEFT">348.03</TD>
		<TD ALIGN="LEFT">7</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1250.76</TD>
		<TD ALIGN="LEFT">1431.26</TD>
		<TD ALIGN="LEFT">8</TD>
	</TR>
</TABLE>
</P>
<P>The previous examples created a savepoint called <TT>SAVE_IT</TT>. An update was
made to the database that changed the value of the <TT>CURR_BAL</TT> column of the
<TT>BALANCES</TT> table. You then saved this change as a savepoint. Following this
save, you executed a <TT>DELETE</TT> statement, but you rolled the transaction back
to the savepoint immediately thereafter. Then you executed <TT>COMMIT TRANSACTION</TT>,
which committed all commands up to the savepoint. Had you executed a <TT>ROLLBACK
TRANSACTION</TT> after the <TT>ROLLBACK TRANSACTION savepoint_name</TT> command,
the entire transaction would have been rolled back and no changes would have been
made.</P>
<P>This example uses Oracle SQL syntax:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;</B>
SQL&gt; <B>UPDATE BALANCES SET CURR_BAL = 25000 WHERE ACCOUNT_ID = 5;</B>
SQL&gt; <B>SAVEPOINT save_it;</B>
SQL&gt; <B>DELETE FROM BALANCES WHERE ACCOUNT_ID = 5;</B>
SQL&gt; <B>ROLLBACK TO SAVEPOINT save_it;</B>
SQL&gt; <B>ROLLBACK;</B>
SQL&gt; <B>SELECT * FROM BALANCES;
</B></FONT></PRE>
<H4><FONT COLOR="#000077">The BALANCES table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Average_Bal</B></TD>
		<TD ALIGN="LEFT"><B>Curr_Bal</B></TD>
		<TD ALIGN="LEFT"><B>Account_ID</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1298.53</TD>
		<TD ALIGN="LEFT">854.22</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">5427.22</TD>
		<TD ALIGN="LEFT">6015.96</TD>
		<TD ALIGN="LEFT">2</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">211.25</TD>
		<TD ALIGN="LEFT">190.01</TD>
		<TD ALIGN="LEFT">3</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">73.79</TD>
		<TD ALIGN="LEFT">25.87</TD>
		<TD ALIGN="LEFT">4</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1285.90</TD>
		<TD ALIGN="LEFT">1473.75</TD>
		<TD ALIGN="LEFT">5</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1234.56</TD>
		<TD ALIGN="LEFT">1543.67</TD>
		<TD ALIGN="LEFT">6</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">345.25</TD>
		<TD ALIGN="LEFT">348.03</TD>
		<TD ALIGN="LEFT">7</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1250.76</TD>
		<TD ALIGN="LEFT">1431.26</TD>
		<TD ALIGN="LEFT">8</TD>
	</TR>
</TABLE>
</P>
<P>This example uses Sybase SQL syntax:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>begin transaction</B>
2&gt; <B>update BALANCES set Curr_Bal = 25000 where Account_ID = 5</B>
3&gt; <B>save transaction save_it</B>
4&gt; <B>delete from BALANCES where Account_ID = 5
</B>5&gt; <B>rollback transaction save_it</B>
6&gt; <B>rollback transaction</B>
7&gt; <B>go</B>
1&gt; <B>select * from BALANCES</B>
2&gt; <B>go</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">The BALANCES table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Average_Bal</B></TD>
		<TD ALIGN="LEFT"><B>Curr_Bal</B></TD>
		<TD ALIGN="LEFT"><B>Account_ID</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1298.53</TD>
		<TD ALIGN="LEFT">854.22</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">5427.22</TD>
		<TD ALIGN="LEFT">6015.96</TD>
		<TD ALIGN="LEFT">2</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">211.25</TD>
		<TD ALIGN="LEFT">190.01</TD>
		<TD ALIGN="LEFT">3</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">73.79</TD>
		<TD ALIGN="LEFT">25.87</TD>
		<TD ALIGN="LEFT">4</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1285.90</TD>
		<TD ALIGN="LEFT">1473.75</TD>
		<TD ALIGN="LEFT">5</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1234.56</TD>
		<TD ALIGN="LEFT">1543.67</TD>
		<TD ALIGN="LEFT">6</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">345.25</TD>
		<TD ALIGN="LEFT">348.03</TD>
		<TD ALIGN="LEFT">7</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1250.76</TD>
		<TD ALIGN="LEFT">1431.26</TD>
		<TD ALIGN="LEFT">8</TD>
	</TR>
</TABLE>

<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>A transaction can be defined as an organized unit of work. A transaction usually
performs a series of operations that depend on previously executed operations. If
one of these operations is not executed properly or if data is changed for some reason,
the rest of the work in a transaction should be canceled. Otherwise, if all statements
are executed correctly, the transaction's work should be saved.</P>
<P>The process of canceling a transaction is called a rollback. The process of saving
the work of a correctly executed transaction is called a commit. SQL syntax supports
these two processes through syntax similar to the following two statements:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">BEGIN TRANSACTION
     statement 1
     statement 2
     statement 3
ROLLBACK TRANSACTION
</FONT></PRE>
<P>or</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">BEGIN TRANSACTION
     statement 1
     statement 2
     statement 3
COMMIT TRANSACTION
</FONT></PRE>
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q If I have a group of transactions and one transaction is unsuccessful, will
	the rest of the transactions process?</B>
	<P><B>A</B> No. The entire group must run successfully.</P>
	<P><B>Q After issuing the <TT>COMMIT</TT> command, I discovered that I made a mistake.
	How can I correct the error?</B></P>
	<P><B>A</B> Use the <TT>DELETE</TT>, <TT>INSERT</TT>, and <TT>UPDATE</TT> commands.</P>
	<P><B>Q Must I issue the<TT> COMMIT</TT> command after every transaction?</B></P>
	<P><B>A</B> No. But it is safer to do so to ensure that no errors were made and no
	previous transactions are left hanging.
</DL>

<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1.</B> When nesting transactions, does issuing a <TT>ROLLBACK TRANSACTION</TT>
	command cancel the current transaction and roll back the batch of statements into
	the upper-level transaction? Why or why not?
	<P><B>2.</B> Can savepoints be used to &quot;save off&quot; portions of a transaction?
	Why or why not?</P>
	<P><B>3.</B> Can a <TT>COMMIT</TT> command be used by itself or must it be embedded?</P>
	<P><B>4.</B> If you issue the <TT>COMMIT</TT> command and then discover a mistake,
	can you still use the <TT>ROLLBACK</TT> command?</P>
	<P><B>5. </B>Will using a savepoint in the middle of a transaction save all that
	happened before it automatically?
</DL>

<H3><FONT COLOR="#000077">Exercises</FONT></H3>

<DL>
	<DD><B>1. </B>Use Personal Oracle7 syntax and correct the syntax (if necessary) for
	the following:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>START TRANSACTION</B>
     <B>INSERT INTO CUSTOMERS VALUES
     ('SMITH', 'JOHN')</B>
SQL&gt; <B>COMMIT;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>2. </B>Use Personal Oracle7 syntax and correct the syntax (if necessary) for
	the following:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;
     UPDATE BALANCES SET CURR_BAL = 25000;</B>
SQL&gt; <B>COMMIT;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>3.</B> Use Personal Oracle7 syntax and correct the syntax (if necessary) for
	the following:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;
     INSERT INTO BALANCES VALUES
     ('567.34', '230.00', '8');</B>
SQL&gt; <B>ROLLBACK;</B></FONT></PRE>

</BLOCKQUOTE>

<H1><FONT COLOR="#0066FF"><B></B></FONT></H1>
<CENTER>
<P>
<HR>
<A HREF="../ch10/ch10.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch12/ch12.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>