ch13.htm

Learn SQL in an easy way...

Suppose that when you delete an artist's only record from the <TT>RECORDINGS</TT>
table, you also want to delete the artist from the <TT>ARTISTS</TT> table. If the
records have already been deleted when the trigger is fired, how do you know which
<TT>Artist_ID</TT> should be deleted? There are two methods to solve this problem:

<UL>
	<LI>Delete all the artists from the <TT>ARTISTS</TT> table who no longer have any
	recordings in the <TT>RECORDINGS</TT> table. (See Example 13.10a.)
</UL>


<UL>
	<LI>Examine the deleted logical table. Transact-SQL maintains two tables: <TT>DELETED</TT>
	and <TT>INSERTED</TT>. These tables, which maintain the most recent changes to the
	actual table, have the same structure as the table on which the trigger is created.
	Therefore, you could retrieve the artist IDs from the <TT>DELETED</TT> table and
	then delete these IDs from the <TT>ARTISTS</TT> table. (See Example 13.10b.)
</UL>

<H4><FONT COLOR="#000077">Example 13.10a</FONT></H4>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create trigger delete_artists</B>
2&gt;<B> on RECORDINGS</B>
3&gt;<B> for delete as</B>
4&gt; <B>begin</B>
5&gt;     <B> delete from ARTISTS where artist_id not in</B>
6&gt;     <B> (select artist_id from RECORDINGS)</B>
7&gt; <B>end</B>
8&gt; <B>go</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">Example 13.10b</FONT></H4>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create trigger delete_artists</B>
2&gt; <B>on RECORDINGS
</B>3&gt; <B>for delete as</B>
4&gt; <B>begin</B>
5&gt;      <B>delete ARTISTS from ARTISTS, deleted</B>
6&gt;      <B>where ARTIST.artist_id  = deleted.artist_id
</B>7&gt; <B>end</B>
8&gt;<B> go</B>
</FONT></PRE>
<H3><FONT COLOR="#000077">Restrictions on Using Triggers</FONT></H3>
<P>You must observe the following restrictions when you use triggers:

<UL>
	<LI>Triggers cannot be created on temporary tables.
	<P>
	<LI>Triggers must be created on tables in the current database.
	<P>
	<LI>Triggers cannot be created on views.
	<P>
	<LI>When a table is dropped, all triggers associated with that table are automatically
	dropped with it.
</UL>

<H3><FONT COLOR="#000077">Nested Triggers</FONT></H3>
<P>Triggers can also be nested. Say that you have created a trigger to fire on a
delete, for instance. If this trigger itself then deletes a record, the database
server can be set to fire another trigger. This approach would, of course, result
in a loop, ending only when all the records in the table were deleted (or some internal
trigger conditions were met). Nesting behavior is not the default, however. The environment
must be set to enable this type of functionality. Consult your database server's
documentation for more information on this topic.
<H2><FONT COLOR="#000077">Using SELECT Commands with UPDATE and DELETE</FONT></H2>
<P>Here are some complex SQL statements using <TT>UPDATE</TT> and <TT>DELETE</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPPDATE EMPLOYEE_TBL
     SET LAST_NAME = 'SMITH'
     WHERE EXISTS (SELECT EMPLOYEE_ID
     FROM PAYROLL_TBL
     WHERE EMPLOYEE_ID = 2);</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1 row updated.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>EMPLOYEE</TT> table had an incorrect employee name. We updated the <TT>EMPLOYEE</TT>
table only if the payroll table had the correct ID.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE EMPLOYEE_TABLE
     SET HOURLY_PAY = 'HOURLY_PAY * 1.1
     WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID
     FROM PAYROLL_TBL
     WHERE EMPLOYEE_ID = '222222222');</B>

1 row updated.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>We increased the employee's hourly rate by 10 percent.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DELETE FROM EMPLOYEE_TBL
	WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID
	FROM PAYROLL_TBL
	WHERE EMPLOYEE_ID = '222222222';</B></FONT><FONT COLOR="#000000"></FONT></PRE>

<PRE><FONT COLOR="#0066FF">1 row deleted.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Here we deleted an employee with the ID of <TT>222222222</TT>.
<H3><FONT COLOR="#000077">Testing SELECT Statements Before Implementation</FONT></H3>
<P>If you are creating a report (using SQL*PLUS for an example) and the report is
rather large, you may want to check spacing, columns, and titles before running the
program and wasting a lot of time. A simple way of checking is to add <TT>where rownum
&lt; 3</TT> to your SQL statement:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; select *
     from employee_tbl
     where rownum &lt; 5;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You get the first four rows in the table from which you can check the spelling
and spacing to see if it suits you. Otherwise, your report may return hundreds or
thousands of rows before you discover a misspelling or incorrect spacing.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>A major part of your job--probably
	50 percent--is to figure out what your customer really wants and needs. Good communication
	skills and a knowledge of the particular business that you work for will complement
	your programming skills. For example, suppose you are the programmer at a car dealership.
	The used car manager wants to know how many vehicles he has for an upcoming inventory.
	You think (to yourself): Go count them. Well, he asked for how many vehicles he has;
	but you know that for an inventory the manager really wants to know how many types
	(cars, trucks), models, model year, and so on. Should you give him what he asked
	for and waste your time, or should you give him what he needs? 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">Embedded SQL</FONT></H2>
<P>This book uses the term embedded SQL to refer to the larger topic of writing actual
program code using SQL--that is, writing stored procedures embedded in the database
that can be called by an application program to perform some task. Some database
systems come with complete tool kits that enable you to build simple screens and
menu objects using a combination of a proprietary programming language and SQL. The
SQL code is embedded within this code.</P>
<P>On the other hand, embedded SQL commonly refers to what is technically known as
Static SQL.
<H3><FONT COLOR="#000077">Static and Dynamic SQL</FONT></H3>
<P>Static SQL means embedding SQL statements directly within programming code. This
code cannot be modified at runtime. In fact, most implementations of Static SQL require
the use of a precompiler that fixes your SQL statement at runtime. Both Oracle and
Informix have developed Static SQL packages for their database systems. These products
contain precompilers for use with several languages, including the following:

<UL>
	<LI>C
	<P>
	<LI>Pascal
	<P>
	<LI>Ada
	<P>
	<LI>COBOL
	<P>
	<LI>FORTRAN
</UL>

<P>Some advantages of Static SQL are

<UL>
	<LI>Improved runtime speed
</UL>


<UL>
	<LI>Compile-time error checking
</UL>

<P>The disadvantages of Static SQL are that

<UL>
	<LI>It is inflexible.
	<P>
	<LI>It requires more code (because queries cannot be formulated at runtime).
	<P>
	<LI>Static SQL code is not portable to other database systems (a factor that you
	should always consider).
</UL>

<P>If you print out a copy of this code, the SQL statements appear next to the C
language code (or whatever language you are using). Program variables are bound to
database fields using a precompiler command. See Example 13.11 for a simple example
of Static SQL code.</P>
<P>Dynamic SQL, on the other hand, enables the programmer to build an SQL statement
at runtime and pass this statement off to the database engine. The engine then returns
data into program variables, which are also bound at runtime. This topic is discussed
thoroughly on Day 12.
<H4><FONT COLOR="#000077">Example 13.11</FONT></H4>
<P>This example illustrates the use of Static SQL in a C function. Please note that
the syntax used here does not comply with the ANSI standard. This Static SQL syntax
does not actually comply with any commercial product, although the syntax used is
similar to that of most commercial products.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>BOOL Print_Employee_Info (void)
{
int Age = 0;
char Name[41] = &quot;\0&quot;;
char Address[81] = &quot;\0&quot;;
/* Now Bind Each Field We Will Select To a Program Variable */
#SQL BIND(AGE, Age)
#SQL BIND(NAME, Name);
#SQL BIND(ADDRESS, Address);
/* The above statements &quot;bind&quot; fields from the database to variables from the program.
 After we query the database, we will scroll the records returned
and then print them to the screen */

#SQL SELECT AGE, NAME, ADDRESS FROM EMPLOYEES;

#SQL FIRST_RECORD
if (Age == NULL)
{
     return FALSE;
}
while (Age != NULL)
{
     printf(&quot;AGE = %d\n, Age);
     printf(&quot;NAME = %s\n, Name);
     printf(&quot;ADDRESS = %s\n&quot;, Address);
     #SQL NEXT_RECORD
}
return TRUE;

}</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>After you type in your code and save the file, the code usually runs through some
type of precompiler. This precompiler converts the lines that begin with the <TT>#SQL</TT>
precompiler directive to actual C code, which is then compiled with the rest of your
program to accomplish the task at hand.</P>
<P>If you have never seen or written a C program, don't worry about the syntax used
in Example 13.11. (As was stated earlier, the Static SQL syntax is only pseudocode.
Consult the Static SQL documentation for your product's actual syntax.)
<H2><FONT COLOR="#000077">Programming with SQL</FONT></H2>
<P>So far, we have discussed two uses for programming with SQL. The first, which
was the focus of the first 12 days of this book, used SQL to write queries and modify
data. The second is the capability to embed SQL statements within third- or fourth-generation
language code. Obviously, the first use for SQL is essential if you want to understand
the language and database programming in general. We have already discussed the drawbacks
to using embedded or Static SQL as opposed to Dynamic SQL. Day 18, &quot;PL/SQL:
An Introduction,&quot; and Day 19 &quot;Transact-SQL: An Introduction,&quot; cover
two extensions to SQL that you can use instead of embedded SQL to perform the same
types of functions discussed in this section.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>The popularity of programming environments such as Visual Basic, Delphi, and PowerBuilder
gives database programmers many tools that are great for executing queries and updating
data with a database. However, as you become increasingly involved with databases,
you will discover the advantages of using the tools and topics discussed today. Unfortunately,
concepts such as cursors, triggers, and stored procedures are recent database innovations
and have a low degree of standardization across products. However, the basic theory
of usage behind all these features is the same in all database management systems.</P>
<P>Temporary tables are tables that exist during a user's session. These tables typically
exist in a special database (named <TT>tempdb</TT> under SQL Server) and are often
identified with a unique date-time stamp as well as a name. Temporary tables can
store a result set from a query for later usage by other queries. Performance can
erode, however, if many users are creating and using temporary tables all at once,
owing to the large amount of activity occurring in the <TT>tempdb</TT> database.</P>
<P>Cursors can store a result set in order to scroll through this result set one
record at a time (or several records at a time if desired). The <TT>FETCH</TT> statement
is used with a cursor to retrieve an individual record's data and also to scroll
the cursor to the next record. Various system variables can be monitored to determine
whether the end of the records has been reached.</P>
<P>Stored procedures are database objects that can combine multiple SQL statements
into one function. Stored procedures can accept and return parameter values as well
as call other stored procedures. These procedures are executed on the database server
and are stored in compiled form in the database. Using stored procedures, rather
than executing standalone queries, improves performance.</P>
<P>Triggers are special stored procedures that are executed when a table undergoes
an <TT>INSERT</TT>, a <TT>DELETE</TT>, or an <TT>UPDATE</TT> operation. Triggers
often enforce referential integrity and can also call other stored procedures.</P>
<P>Embedded SQL is the use of SQL in the code of an actual program. Embedded SQL
consists of both Static and Dynamic SQL statements. Static SQL statements cannot
be modified at runtime; Dynamic SQL statements are subject to change.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q If I create a temporary table, can any other users use my table?</B>
	<P><B>A</B> No, the temporary table is available only to its creator.</P>
	<P><B>Q Why must I close and deallocate a cursor?</B></P>
	<P><B>A </B>Memory is still allocated for the cursor, even though its name may no
	longer exist.
</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>True or False: Microsoft Visual C++ allows programmers to call the
	ODBC API directly.
	<P><B>2. </B>True or False: The ODBC API can be called directly only from a C program.</P>
	<P><B>3. </B>True or False: Dynamic SQL requires the use of a precompiler.</P>
	<P><B>4. </B>What does the <TT>#</TT> in front of a temporary table signify?</P>
	<P><B>5.</B> What must be done after closing a cursor to return memory?</P>
	<P><B>6.</B> Are triggers used with the <TT>SELECT</TT> statement?</P>
	<P><B>7. </B>If you have a trigger on a table and the table is dropped, does the
	trigger still exist?
</DL>

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

<DL>
	<DD><B>1.</B> Create a sample database application. (We used a music collection to
	illustrate these points today.) Break this application into logical data groupings.
	<P><B>2.</B> List the queries you think will be required to complete this application.</P>
	<P><B>3.</B> List the various rules you want to maintain in the database.</P>
	<P><B>4.</B> Create a database schema for the various groups of data you described
	in step 1.</P>
	<P><B>5.</B> Convert the queries in step 2 to stored procedures.</P>
	<P><B>6.</B> Convert the rules in step 3 to triggers.</P>
	<P><B>7.</B> Combine steps 4, 5, and 6 into a large script file that can be used
	to build the database and all its associated procedures.</P>
	<P><B>8.</B> Insert some sample data. (This step can also be a part of the script
	file in step 7.)</P>
	<P><B>9.</B> Execute the procedures you have created to test their functionality.
</DL>

<H1></H1>
<CENTER>
<P>
<HR>
<A HREF="../ch12/ch12.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch14/ch14.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>