InterBase Quick Start: Part V - More Practice with Generators and Triggers

From InterBase
Jump to: navigation, search

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


The remainder of this section on triggers takes you through the process of creating another generator and three more triggers. There are two ways to complete this section:

  • Follow the insctructions and manually enter the remaining items.
  • Use the TRIGGERS.SQL script.

Using the Script

If you choose to use the script do the following:

  1. Load the TRIGGERS.SQL script.
  2. Execute the query.
  3. If you have successfully executed the TRIGGER.SQL script, we recommend that you back up your database.
  4. Advance to the page Stored Procedures.

Manually Entering the Remaining Items

We recommend that you commit your work every time after you sucessfully create a trigger or a stored procedure.

Image 025.jpgMore Practice with Generators and Triggers

  1. The next trigger that you create uses the cust_no_gen generator.
    CREATE generator cust_no_gen
    
    SET generator cust_no_gen TO 1015
    
  2. Now execute the following CREATE TRIGGER statement to create the set_cust_no trigger:
    CREATE TRIGGER set_cust_no FOR customer
    BEFORE INSERT AS
    BEGIN
        NEW.cust_no = gen_id
           (
                cust_no_gen,
                1
           );
    END
    
  3. To test this trigger, first confirm that the highest customer number is 1015:
    SELECT MAX(cust_no)
    FROM   customer
    
  4. Then insert the following row:
    INSERT INTO customer
                (customer,
                 contact_first,
                 contact_last,
                 phone_no,
                 address_line1,
                 address_line2,
                 city,
                 state_province,
                 country,
                 postal_code,
                 on_hold)
    VALUES      ('Big Rig',
                 'Henry',
                 'Erlig',
                 '701-555-1212',
                 '100 Big Rig Way',
                 NULL,
                 'Atlanta',
                 'GA',
                 'USA',
                 '70008',
                 NULL)
    
  5. To confirm that the new customer number is 1016, enter the following statement:
    SELECT cust_no
    FROM   customer
    WHERE  customer = 'Big Rig'
    

Advance To:

Creating a Trigger to Maintain Change Records