InterBase Quick Start: Part V - Creating a Generator
Go Up to InterBase Quick Start: Part V - Advanced Topics
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
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.