InterBase Quick Start: Part V - Creating a Generator

From InterBase
Jump to: navigation, search

Go Up to InterBase Quick Start: Part V - Advanced Topics


Image 025.jpg 1. Begin by checking the employee numbers in the Employee table, to confirm that the highest employee number currently in use is 145:

SELECT emp_no from Employee 
 ORDER BY emp_no
Note: The statement above returns all the employee numbers so that you can confirm that 145 is the highest. The following statement produces the same information more efficiently:
SELECT max(emp_no) from Employee

2. Triggers often use generators, and the trigger you create in the next exercise is an example of one. Execute the following statement to create a generator called emp_no_gen:

CREATE GENERATOR emp_no_gen

3. Now initialize the generator to 145, the highest value currently in use:

 SET GENERATOR emp_no_gen TO 145

Image 025.jpg Creating a Trigger that Generates a Value

1. The next statements define a trigger named set_emp_no that makes use of the emp_no_gen generator to generate unique sequential employee numbers and insert them into the Employee table:

CREATE TRIGGER set_emp_no FOR Employee
 BEFORE INSERT AS 
 BEGIN 
 New.emp_no = gen_id(emp_no_gen, 1); 
 END
This statement says that the set_emp_no trigger will fire before an insert operation, and that it will create a new value for emp_no by calling the gen_id() function on the emp_no_gen generator with an increment of 1.

2. To test the generator, execute the following INSERT statement:

 INSERT INTO Employee (first_name, last_name, dept_no, job_code,     job_grade,      job_country, hire_date, salary, phone_ext)  
 VALUES ('Reed', 'Richards', '671', 'Eng', 5, 'USA', '07/27/95', 
 '34000', '444')
Notice that you did not include a value for the emp_no column in the INSERT statement. Look at the new record by entering
SELECT * from Employee WHERE last_name = 'Richards'
The employee number is 146. Remember that the highest employee number before you created the generator and inserted a new row was 145. The trigger has automatically assigned the new employee the next employee number.

3. If your INSERT ran without errors and your SELECT returns the correct result set, commit your work by selecting Transactions > Commit.

Finishing the Trigger Exercises

The remainder of this section on triggers takes you through the process of creating another generator and three more triggers. The text instructs you to enter them by hand in order to get more experience with them.

Tip: If you want to save time, you can use the Query > Load Script command to read in the Triggers.sql script in place of entering the remaining trigger and generator statements yourself. Triggers.sql defines another generator and a trigger named set_cust_no that assigns unique customer numbers. It defines two other triggers: save_salary_change and post_new_order.
Whether you choose to enter the remaining trigger statements yourself or to run the script, do take time to open Triggers.sql in a text editor and see that you understand the code in it. Notice that triggers in a script require the use of the SET TERM statement. (See The Structure of Triggers for more information on SET TERM.)
If you choose to work through this section manually instead of running the script, commit your work after creating and testing each trigger or stored procedure.

Advance To:

Image 025.jpg More Practice with Generators and Triggers