ch16.htm

好书《C++ Builder高级编程技术》

        Foreign key(Types) references Types(Code),

        Foreign key(Medium) references Medium(Code)

  );


</FONT></PRE>
<H2><FONT COLOR="#000077">Creating Indexes on the Music Table</FONT></H2>
<P>By now, you have seen most of the data definition for <TT>MUSIC.GDB</TT>. However,
I want to discuss a few more details before moving on to take a look at the 
interface
for the program.</P>
<P>The indexes on the Music database are all defined to enhance your access to the
data and provide automatic sorting of the data. Indexes have no other purpose than
to speed your access to data and help with sorting. 
They allow you to search for
data very quickly. If any of your searches takes too long, one of the best ways to
address the problem is through enhancing your indexes.</P>
<P>It's important to see the difference between the primary and foreign keys 
that
create referential integrity and add constraints to a table and the ordinary indexes,
which speed up access to a particular record. A primary key is an index on speed.
It gives you everything an index gives you and then a little more.</P>
<P>In 
particular, when you create a primary key or foreign key in InterBase (or in
Paradox), then a unique index is automatically created on that key. For instance,
the Artist and Album tables both have unique indexes on the <TT>Code</TT> field.
The Foo 
table, created in the last section, has a unique index on the field called
<TT>Sam</TT>.</P>
<P>One simple way to see these indexes is to open the Database Explorer and examine
the indexes listed under the Album or Artist tables. The Artist table, for 
instance,
has two indexes. One is called <TT>Artist_LastFirst_Ndx</TT>, and I will describe
it later in this chapter. The other index has the strange name <TT>RDB$PRIMARY1</TT>.
This is the index that was created when the code field was designated as 
a primary
key. The only really important part of the name is the word <TT>primary</TT>, which
helps you to understand that this is part of a primary key. The rest is just syntactical
sugar used internally by InterBase.


<BLOCKQUOTE>
	<P>
<HR>
<FONT 
COLOR="#000077"><B>NOTE: </B></FONT>You can add a primary or foreign key after
	a table is created, as long as doing so does not violate any other database rules.
	You should make sure that the tables involved are not in use by another program when
	
you make these kinds of modifications.<BR>
	<BR>
	Here is an example of adding a primary key:</P>
	<PRE><FONT COLOR="#0066FF">ALTER TABLE FOO ADD PRIMARY KEY (Sam);

</FONT></PRE>
	<P>Here is an example of adding a foreign key:</P>
	<PRE><FONT 
COLOR="#0066FF">ALTER TABLE FOO ADD FOREIGN KEY (Foreigner) REFERENCES Book(CODE);</FONT></PRE>

</BLOCKQUOTE>

<PRE><FONT COLOR="#0066FF"></FONT></PRE>


<BLOCKQUOTE>
	<P>The foreign key example shown here assumes that you added an <TT>Integer</TT>
	
field to the Foo table called <TT>Foreigner</TT>. 
<HR>


</BLOCKQUOTE>

<P>Besides the primary keys and foreign keys, the following indexes are also defined
on the Artist and Album tables:</P>
<PRE><FONT COLOR="#0066FF">CREATE INDEX GROUPALBUM_IDX ON 
ALBUM(GROUPCODE, ALBUM);

CREATE INDEX ARTIST_LASTFIRST_NDX ON ARTIST(LAST, FIRST);

</FONT></PRE>
<P>If you want to create a new index in WISQL, you can do so with the SQL <TT>Create
Index</TT> command, as shown in the preceding code. The command 
takes the name of
the index, the name of the table on which the index is enforced, and finally, in
parentheses, the names of the fields in the index. For more information on this and
other commands, see the InterBase Workgroup Server Language 
Reference, or, more practicable,
the online help for WISQL. Also helpful are third-party books such as the Practical
SQL Handbook (ISBN: 0-201-62623-3).</P>
<P>I created these two indexes for different reasons. The <TT>Artist_LastFirst_Ndx</TT>
is 
meant primarily to speed up searches and sorts in the Artist table.</P>
<P>The <TT>GroupAlbum_idx</TT> is created for a more specific reason. Many of the
artists have numerous albums associated with them. I relate the Album table to the
Artist table 
using the standard <TT>MasterSource</TT>, <TT>MasterField</TT>, and
<TT>IndexFieldName</TT> gambit you saw in Chapter 9, &quot;Using <TT>TTable</TT>
and <TT>TDataSet</TT>.&quot; To set up this relationship inside BCB, I need an index
on the 
<TT>Code</TT> field from the Artist table and the <TT>GroupCode</TT> field
from the Album table. Both of those are provided for me automatically by my primary
and foreign keys. However, I try to relate the two tables and also make sure the
records 
from the Album table are sorted correctly. To do this, I need to create a
new index that both relates the Album table to the Artist table and also makes sure
that the Album table is sorted correctly. The <TT>GroupAlbum_Idx</TT> serves this
purpose. (I 
had a little trouble getting the <TT>GroupAlbum_Idx</TT> to work properly
at first, but things cleared up when I closed the Album table and then reopened it.)
<H2><FONT COLOR="#000077">Generators, Triggers, and Stored Procedures</FONT></H2>
<P>The 
next few sections of this chapter deal with triggers and generators. You will
read a good deal about automatically generating values for primary keys and a little
about the relative merits of triggers and generators.</P>
<P>Three generators provide 
unique numbers to use in the <TT>Code</TT> fields of
the Artist, Book, and Album tables. Generators provide almost the same functionality
in InterBase tables that autoincrement fields provide in Paradox tables. That is,
they provide numbers to use in 
the keyed fields that bind tables together.</P>
<P>Autoincrement fields are filled in automatically at runtime. Generators, however,
merely generate random numbers in sequence, where the first number generated might
be one, the second two, and so on. 
You can tell a generator to start generating numbers
at a particular starting value, where the first number might be x, the next x + 1,
and so on.</P>
<P>Here is how you create a generator in WISQL and set it to a particular value:</P>
<PRE><FONT 
COLOR="#0066FF">CREATE GENERATOR MUSIC_GEN;

SET GENERATOR MUSIC_GEN TO 300;

</FONT></PRE>
<P>As a result of this code, the first number generated is 300, the next is 301,
and so on.</P>
<P>I will now show how to write a trigger. The Music program 
uses triggers on the
Artist table but not on the Album table. The reason for splitting things up this
way is explained in this section and in the upcoming section of this chapter called
&quot;Deciding When to Use Triggers.&quot;</P>
<P>Here is how you 
write a trigger that automatically puts this value into the <TT>Code</TT>
field of the Artist table whenever an <TT>Insert</TT> occurs:</P>
<PRE><FONT COLOR="#0066FF">CREATE TRIGGER SETMUSICGEN FOR ARTIST

BEFORE INSERT AS

BEGIN

  NEW.CODE = 
GEN_ID(MUSIC_GEN, 1);

END

</FONT></PRE>
<P>This code appears on the server side. It's not BCB code. You enter it exactly
as shown in WISQL. The procedure runs on the server end; it is not processed by BCB.
There is never any need to call this 
procedure explicitly. The whole point of triggers
is that they run automatically when certain events occur. This one is designed to
run right before an <TT>Insert</TT> occurs. In other words, the way to call this
procedure from BCB is to perform an 
<TT>Insert</TT>.</P>
<P>This code states that you want to create a trigger called <TT>SetMusicGen</TT>
to run on the Artist table. The generator is called before an <TT>insert</TT> operation:</P>
<PRE><FONT COLOR="#0066FF">BEFORE INSERT AS


</FONT></PRE>
<P>The actual body of the code is simple:</P>
<PRE><FONT COLOR="#0066FF">NEW.CODE = GEN_ID(MUSIC_GEN, 1);

</FONT></PRE>
<P>The <TT>NEW</TT> statement says that you are going to define the new value for
the <TT>CODE</TT> field of the 
record that is about to be inserted into a table.
In this case, you reference the new value for the <TT>CODE</TT> field of the Artist
table.</P>
<P><TT>GEN_ID</TT> is a function built into InterBase that produces an integer value.
It takes a generator 
as its first parameter and a step value as its second parameter.
The step value increases or decreases the value produced by the generator. For instance,
the preceding code increments the value by 1.


<BLOCKQUOTE>
	<P>
<HR>
<FONT 
COLOR="#000077"><B>NOTE: </B></FONT>You can get a generator to fill in a field
	automatically with the trigger shown previously. Unfortunately, BCB does not provide
	particularly good support for triggers, in part because each server generates a 
different
	kind of trigger. The developers of BCB didn't want to run around finding out how
	to handle triggers for 30 different kinds of servers and neither did the developers
	of the BDE.<BR>
	<BR>
	Some third-party solutions to this problem include 
a good one that works with InterBase
	called the <TT>IBEventAlerter</TT>. This solution ships with BCB but is found on
	the samples page. Its presence on the samples page means it lives in a never-never
	land between the hard code made by the IDE team 
and the sample code, written by me
	and many others like me, which appears in the <TT>Examples</TT> directory of a standard
	BCB install. 
<HR>


</BLOCKQUOTE>

<P>In the example under discussion, BCB's poor support for triggers is not crucial
because 
the table is not sorted on the <TT>Code</TT> field. If it were, this trigger
might cause BCB to lose track of the current record after the insert operation. BCB
would not know that the <TT>Code</TT> value was inserted because it would not know
that 
the trigger fired. As a result, the current record might be lost because it
was sorted on a value of which BCB was not aware. In other words, the index would
cause the record to be moved to a particular place in the dataset, but BCB would
not know how 
to follow it. As far as BCB is concerned, the <TT>Code</TT> field is
still blank!</P>
<P>There may be a problem with BCB reporting a referential integrity error because
the field appears to be blank and yet the field is defined as <TT>Not Null</TT>.

Underneath, the field is not really blank, but it appears that way to BCB. If this
problem occurs, you can fix it by filling in the field with any randomly chosen value
in an <TT>OnAfterInsert</TT> response method:</P>
<PRE><FONT COLOR="#0066FF">void 
__fastcall TDMod::ArtistTableAfterInsert(TDataSet *DataSet)

{

  ArtistTableCODE-&gt;AsInteger = 0;

}

</FONT></PRE>
<P>Here it doesn't matter what you set for the <TT>CODE</TT> field, as long as some
value is there so that the <TT>Not Null</TT> 
constraint is satisfied. The correct
value is filled in later by the trigger.</P>
<P>Unfortunately, that is not yet the end of the story. The grid that most programmers
use to view a table depends on the current image of a table reported by the VCL.

As a result, having a field updated by a trigger can confuse the grid and cause it
to show more than one copy of a record. This leads to all kinds of confusion. The
best solution is to either call <TT>Refresh</TT> after posting an insert or else
to 
close and then reopen the table. This latter step is not quite as drastic as it
may seem because the <TT>TDatabase</TT> object keeps you connected to the database
even if you close a table.</P>
<P>The key point to grasp is that this is one of the 
cases where it is possible to
use a trigger to update an index inside BCB. It works because the table is sorted
on the <TT>Last</TT> and <TT>First</TT> fields, not on the <TT>CODE</TT> field. Therefore,
you can use a trigger to fill in the 
<TT>CODE</TT> field. If you tried to fill in
the <TT>Last</TT> field with a trigger, then there would be trouble!


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE: </B></FONT>Here is another example of how to create
	a trigger using WISQL:</P>
	
<PRE><FONT COLOR="#0066FF">CREATE TRIGGER SET_COMPANY_UPPER FOR COMPANY

ACTIVE BEFORE INSERT POSITION 1

AS

BEGIN

  NEW.COMPANY_UPPER = UPPER(NEW.COMPANY);

END</FONT></PRE>

</BLOCKQUOTE>

<PRE><FONT COLOR="#0066FF"></FONT></PRE>


<BLOCKQUOTE>
	
<P>This code is called just before an insert operation on a table called <TT>Company</TT>.
	This table contains a string field also called <TT>Company</TT> and a second field
	called <TT>Company_Upper</TT>. The second field is meant to mirror the 
<TT>Company</TT>
	field but with all its characters in uppercase. Having this second field takes up
	a lot of space, but it allows you to conduct searches and sorts on the <TT>Company</TT>
	field without taking into account character case. The goal of 
the trigger shown previously
	is to take the new value for the <TT>Company</TT> field and convert it into an uppercase
	version of the string for use in the <TT>COMPANY_UPPER </TT>field. The <TT>Upper</TT>
	macro shown here is built into 
InterBase.<BR>
	<BR>
	Notice the line that states when this trigger is fired:</P>
	<PRE><FONT COLOR="#0066FF">ACTIVE BEFORE INSERT POSITION 1</FONT></PRE>

</BLOCKQUOTE>

<PRE><FONT COLOR="#0066FF"></FONT></PRE>


<BLOCKQUOTE>
	<P>For a detailed 
understanding of how to create triggers, turn to the Language Reference
	for the InterBase server.<BR>
	<BR>
	I show you this trigger because it works fine under most circumstances when used
	with BCB. BCB does not need to know that the 
<TT>Set_Company_Upper</TT> trigger occurred.
	The trigger can take place in the background without impacting BCB's inner workings.