InterBase Quick Start: Part V - Creating a Generator

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

Creating and Setting a Generator

Check the employee numbers in the Employee table to confirm what the highest employee number currently is:
```
SELECT emp_no
FROM   employee
ORDER  BY emp_no
```
The statement above returns all the employee numbers so that you can confirm that 145 is the highest number. You can reach the same conclusion with less work using the following statement:
```
SELECT MAX(emp_no)
FROM   employee
```
Execute the following statement to create a generator called emp_no_gen:
```
CREATE generator emp_no_gen
```
In order to use the generator properly, you have to initialize it to the highest value that is currently in use (145):
```
SET generator emp_no_gen TO 145
```

Creating a Trigger that Generates a Value

In this section you define a trigger set_emp_no that uses 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
```
The above statement says that the set_emp_no trigger fires before an insert operation, and that it creates a new value for emp_no by calling the gen_id() function on the emp_no_gen generator with an increment of 1.

To test the generator, execute the following 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')

You can see that the statement does not include a value for the emp_no column.

Examine the row that you insterted using the following statement:
```
SELECT *
FROM   employee
WHERE  last_name = 'Richards'
```
The employee number is 146. The trigger automatically assigned the next employee number for the new employee.

We recommend that you commit your work before proceeding.

Advance To:

More Practice with Generators and Triggers