Generators, Triggers, and Stored Procedures
Generators, triggers, and stored procedures (sprocs) can be used to create server-side rules in InterBase. They are used in many operations, ranging from the simple implementation of an autoincrement field in a table to complex SQL operations involving several tables and thousands of records. Because they reside in the database with the data, they typically can perform an operation on the database faster than a similar rule residing in the client.
Generators
Generators can be viewed as global integer variables for the database. They are not tied to any table or field, but merely generate numbers in sequence through the use of the GEN_ID() function. Although generators are not often used on their own, they are extensively used for assigning values to unique keys in triggers and sprocs.
The following will create a generator and set its value:
CREATE GENERATOR PRO_ID_GEN; SET GENERATOR PRO_ID_GEN TO 1;
This code creates the generator PRO_ID_GEN and sets its value to 1 . We will use this generator to supply the Program table with its unique key values.
It is important to realize that generators are not affected by transactions. For example, if you start a transaction and call the GEN_ID() function, rolling back the transaction will not change back the new value of the generator. The new value will remain the same as if the transaction had been committed and retained.
Triggers
An InterBase trigger is similar to a component event. A trigger can be set to execute a command or commands when a row in a table or view is inserted, deleted, or updated. However, a trigger cannot be called directly.
Triggers have many uses: maintaining referential integrity, validating input information, and creating logs of user activities involving the database. One common use of triggers is the implementation of autoincrement fields. InterBase does not come with an autoincrement field type, but this operation can be duplicated by using a normal integer field, the PRO_ID_GEN generator seen earlier, and the trigger in Listing 10.2.
Listing 10.2 SQL to Create One of the Program Table's Triggers
CREATE TRIGGER SET_PRO_ID FOR PROGRAM BEFORE INSERT AS BEGIN IF (NEW.PRO_ID IS NULL) THEN BEGIN NEW.PRO_ID = GEN_ID(PRO_ID_GEN,1); END IF (NEW.PRO_MADE IS NULL) THEN BEGIN NEW.PRO_MADE = 'TODAY'; END END
This trigger is assigned to the Program table to execute before a record is inserted. The NEW variable contains the fields to be inserted. If there is no pro id value supplied, the GEN_ID() function takes the supplied generator, increases it by 1, and returns the new value. This value is then assigned to the new record's pro_id field. If there is no value supplied for the pro_made field, it is assigned the current server date.
The one drawback to assigning the autoincrement field's value in this way is that C++Builder InterBase components don't normally receive notification that the trigger has incremented the field's value. The table must be refreshed, the record must be refreshed, or some other means must be found to make this information viewable to the application. There are several methods of addressing this drawback.
In C++ Builder 6, the new property is provided on the TIBQuery or TIBTable component. The TIBCustomDataset 's TIBGeneratorField type of property is GeneratorField . GeneratorField identifies one of the TIBCustomDataset 's field objects as a field to be refreshed directly from the table after insert. It is assumed that this field is updated because of the execution of a generator in a stored procedure in the Interbase database.
Prior to C++Builder 6 the only method of accomplishing this was through the use of stored procedures. The following section discusses the method to introduce the concept of stored procedures, and how they can interact with the IBExpress components.
Stored Procedures
A stored procedure ( sproc ) is a routine that resides in the database. Sprocs can be created to perform operations on the database that return anything from a single value to multiple rows of information. For example, Listing 10.3 shows a procedure that takes the supplied name of a program to be created, generates a new record with that information and the PRO_ID_GEN generator, and returns the program's pro_id field.
Listing 10.3 SQL to Create the Create_Program Stored Procedure
CREATE PROCEDURE CREATE_PROGRAM /*name of procedure*/ (THE_PRO_NAME CHAR(80))/*supplied params*/ RETURNS (THE_PRO_ID INTEGER)/*returned params*/ AS BEGIN THE_PRO_ID = GEN_ID(PRO_ID_GEN,1);/*get the next program id*/ INSERT INTO PROGRAM(PRO_ID,PRO_NAME,PRO_MADE) VALUES(:THE_PRO_ID,:THE_PRO_NAME,'TODAY');/*insert the new record*/ END
This procedure is a better solution than the trigger we created previously because it solves the problem of retrieving the newly created record's PRO_ID . Like a trigger, this procedure is stored in the database, and its internal workings can be changed without having to update any of the clients . We will use both triggers and sprocs in Bug Tracker.
NOTE
There might be times when you want to retrieve a generator's value through a normal query statement. To accomplish this, you would use a statement such as
SELECT GEN_ID(PRO_ID_GEN,1) NEXT_PRO_ID FROM RDB$DATABASE
When this statement is executed in a TIBQuery , it will return the field NEXT_PRO_ID , which will contain the next PRO_ID_GEN generator's value.
Another alternative to using stored procedures with triggers, or the GeneratorField property, is to use the ForcedRefresh property of IBExpress. ForcedRefresh is an easy way to bring back changes made by a trigger. The key to using ForcedRefresh is that the client needs to have knowledge of a secondary key, and the lookup for the WHERE clause of the RefreshSQL should be on the secondary key instead of the generated primary key. This is explored in more detail in the "modify, delete, insert, refresh" section, later in this chapter.
It is generally a good rule of thumb not to allow the client-side to modify primary keys. If the client-side needs to modify a table's primary key, that table is a prime candidate for a generated primary key. The modifiable part should be a secondary key.
 |
| |
| Top |
