28 CHAPTER 7.DATABASE ARCHITECTURES
7.5 Inserting Records
Usually,when the user enters something new into a database,your application
also enters a unique ID associated to it.However,entering a new item into a
database is not an atomic act.Typically,the user lls in a form with relevant
data that will ultimately be stored in several tables.If part-way through this
operation the user decides to cancel the entry,you have to decide what to do
about the unique ID.In essence,there are two ways to deal with this situation:
1.you can defer assigning the number until the user completes some action,
like clicking an OK button,or
2.you can assign the unique ID at the beginning and simply throw it away
when the user cancels.
The rst choice,deferring the assignment of an ID number,is in some sense
the more aesthetically pleasing,but in most cases,it is incredibly dicult to
program.This unique ID is usually used as a primary key and thus links infor-
mation regarding the new item in several tables.When the new item is created,
its ID typically defaults to 0,both in the main table and in the linked tables.
When the user completes and accepts his entries,a unique ID must be assign.
As soon this is done for one of the tables,its association with the others is bro-
ken,because now its primary key diers from one used in the other tables.This
always creates diculties for your application.The only way to circumvent this
is to unlink the tables before inserting a new item.Afterward,you must re-link
the tables,so when you are viewing or editing items,the relevant pieces of data
will stay together.If some of the tables are in a one-to-many relationship,this
can cause quite a bit of diculty,because as you move among the records,the
database engine will be posting records with the default unique ID.Unless these
records are either removed or corrected,they will corrupt your data.
The second choice,assigning an ID number that may be thrown away if
the user cancels,results in wasted ID numbers.You may be tempted to try to
recover these;don't.Even if the unique ID is simply an unsigned 32-bit integer,
there are over 2 billion such numbers,enough to assign 8 to every man,woman,
and child in the United States.If the number of items in your population is
large enough that it would come even close to needing more ID numbers,your
other problems will dwarf the potential of wasting a few ID numbers here and
there.Go ahead and waste them.
Within the context of Delphi and InterBase,one way to handle the assign-
ment of unique ID numbers is the following.It involves Generators,Stored
Procedures,and Triggers on the InterBase side and writing some related code
on the Delphi side.
Consider the case of adding a new Agency to the Impact database.We will
want to have a unique ID for the agency,and for this we rst have to create
a generator,NextAgID.This is easily done with create generator NextAgID.
This creates the generator and initializes it to zero.Each time your program
executes the InterBase function gen
id(NextAgID,1),the generator NextAgID
is incremented by one and the new value returned.Thus,when we want to
insert an agency into the Agency table,we can obtain a new ID number for it
Draft|Do Not Quote 10 October 1999
7.5.INSERTING RECORDS 29
by executing the stored procedure
create procedure GetNextAgID returns (NextAgIDVal integer) as
NextAgIDVal = gen_id(NextAgID,1);
This procedure returns the new value associated with NextAgID.To use
it within Delphi,you rst put a StoredProc component on the DataModule,
DM.Assume it is named GetNextAgIDProc.Then,to obtain a new Agency ID
number and store it into an Integer named CurrentAgID,you execute the code
Because there may be other ways of entering data into the Agency table,
it is a good idea to create a Trigger procedure that will ensure that the same
NextAgID generator is used whenever one of these methods adds an agency to
the table.The following Trigger will re before any attempt to insert into the
Agency table.Because it is in\position 0",it will re before any other Trigger
that might be set to re before inserting data into the Agency table.
Note that,as far as our Delphi programming is concerned,this is not nec-
essary;it is just defensive programming.Since the new.AgID is checked against
null,if we are careful always to use the ID number returned by GetNextAgID,
the SetAgID Trigger will do nothing to data we insert.
create trigger SetAgID for Agency
active before insert position 0 as
if (new.AgID is null) then
new.AgID = gen_id(NextAgID,1);
Draft|Do Not Quote 10 October 1999