InterBase Quick Start: Part V - More Practice with Generators and Triggers
Go Up to InterBase Quick Start: Part V - Advanced Topics
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) 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. 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'