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

Question

I'm starting a little with SQL, so please carry me on this. My project is as follows:

Using MSSQL on Windows Server 2008 R2

There is an existing database table — call her PRODUCT — that contains several thousand rows of data, which is the product information for each product sold by X. The three columns that interest me are ITEMGROUPID, ITEMID, and ITEMNAME. ITEMID is the primary key for this table and is a unique product code. ITEMGROUPID indicates which product category each item belongs to, and ITEMNAME itself. I'm only interested in one product category, so using ITEMGROUPID, I can determine how many rows my table will have (currently 260).

Now I am creating a table containing some parameters for creating each of these products - let it LINEPARAMETERS. For example, when we create Widget A, we need Pipeline B to run at speed C. I intend to create a foreign key in my table by specifying ITEMID in another table. Therefore, each row in my new table will refer to a specific product in the existing product database.

My question is: if a new product is being developed that meets my criteria (ITEMGROUPID = "VALUE") and entered into an existing table using ITEMID, is there a way for my table to automatically create a new row with this ITEMID and default values ​​in all other columns?

Answer 1

You can create a trigger that fires when inserted into product and inserts a line into lineparameters , for example:

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

but the best option is to create a foreign key from the product table in the default table for groups, so a row must exist in the default table before inserting the product table, for example:

 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 robust process and ensure that even if someone (silently) disables / removes the trigger, you will not have problems with data integrity.