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.jpgCreating and Setting a Generator

  1. 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
    
  2. Execute the following statement to create a generator called emp_no_gen:
    CREATE generator emp_no_gen
    
  3. 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
    

Image 025.jpgCreating a Trigger that Generates a Value

  1. 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.

  2. 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.

  3. 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