More Practice with Generators and Triggers

More Practice with Generators and Triggers

1. The next trigger that you will create uses the cust_no_gen generator.

Note: Execute each statement in turn to create and initialize this generator:

CREATE GENERATOR cust_no_gen

SET GENERATOR cust_no_gen to 1015

Remember, these are two separate statements, and you must execute each one before entering the next.

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) WHERE 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. Now perform the following SELECT to confirm that the new customer number is, as you expect, 1016:

   SELECT cust_no FROM Customer WHERE customer = 'Big Rig'

Advance To:

Creating a Trigger to Maintain Change Records