CODEWITHSUNDEEP
sqlsql-servertriggers
Dmitriy Kudinov
Dmitriy Kudinov

Reputation: 1072

SQLServer triggers

Please help me to write a trigger that adds new rows to a 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 a SQL trigger. I want to do the same on adding new Technics row.

Upvotes: 3

Views: 838

Answers (2)

John Sansom
John Sansom

Reputation: 41819

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.

Upvotes: 1

KM.
KM.

Reputation: 103589

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).

Upvotes: 4

Related Questions