MSSQL - is there a way to automatically add an entry to one table when an entry is added to another?

Question

I'm a bit of an SQL novice, so please bear with me on this one. My project is as follows:

Using MSSQL on Windows Server 2008 R2.

There is an existing database table - let's call it PRODUCTS - which contains several thousand rows of data, which is the product information for every product Company X sells. The three columns I am interested in are ITEMGROUPID, ITEMID and ITEMNAME. The ITEMID is the primary key for this table, and is a unique product code. ITEMGROUPID indicates what category of product each item falls into, and ITEMNAME is self explanatory. I am only interested in one category of product, so by using ITEMGROUPID I can determine how many rows my table will have (currently 260).

I am now creating a table containing some parameters for making each of these products - let's call it LINEPARAMETERS. For example, when we make Widget A, we need Conveyor B to run at Speed C. I intend to create a foreign key in my table, pointing to the ITEMID in the other table. So each row in my new table will reference a specific product in the existing product database.

My question is, if a new product is developed that matches my criteria (ITEMGROUPID = 'VALUE'), and entered into the existing table with an ITEMID, is there any way for my table to automatically generate a new row with that ITEMID and default values in all other columns?

Answer 1

You could create a trigger that fires on insert to product and inserts a row into the lineparameters, like this:

create trigger line_parameter_inserter
on products
after insert
as
    insert into lineparameters (productId, col1, col2)
    values (inserted.id, 'foo', 'bar');

but a better option is to create a foreign key from the product table to your group defaults table, that way a row must exist in the defaults table before you insert the product table, like this:

create table lineparameters (
    id int,
    col1 int,
    ...,
    primary key (id)
)

create table products (
    id int,
    lineparametersId int not null,
    ...
    primary key (id),
    foreign key (lineparametersId) references lineparameters(id)
)

This will create a solid process and ensures that even if someone (silently) disables/deletes the trigger, you won't have data integrity problems.