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

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.

More 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