SQLServer triggers

Question

Please help me to write trigger that, adds new rows in table

I have 3 tables in my database.

1. Regions (id, name); id - primary
2. Technics (id, name); id - primary
3. Availability (id, region, technic, count); id - primary, region - foreign on Regions.id, Technik - foreign on technics.id

I want to add new row in Availability for each Technics row on adding row in Regions.

Somethink like:

procedure void OnAddNewRegion(int region)
{
    foreach (Row r in Technic)
    {
        Availability.Rows.Add(new Row(id, region, r.Id, 0));
    }
}

But in SQL trigger. Same I want to do on the adding new Technics row

Answer 1

try something like this (assuming Availability.id is an identity), which will also handle multiple rows being inserted at one time:

CREATE TRIGGER TR_Regions ON Regions 
FOR INSERT
AS
INSERT INTO Availability 
        (region, technic, count)
    SELECT
        i.id, t.id, 0
        FROM INSERTED            i
            CROSS JOIN Technics  t

GO

you don't say how Regions joins with Technics, so I cross joined them (every inserted Regions, gets one row for every Technics).

Answer 2

In my opinion a cleaner solution for implementing this specific business/insert logic would be to use a Stored Procedure.

Simply create a stored procedure to handle the logic for inserting records to the Region table.