Set a master record (for a one-to-many relationship) for each group

Question

Background

I have two tables with a one to many relationship (Companies.ID (1) - Addresses.CompanyID (N)). The address table also has a “Mainaddress” column that is of type “bit”.

Data examples

Companies:

ID | Name
1  | Company1
2  | Company2

The address:

ID | CompanyID | MainAddress
1  | 1         | true
2  | 1         | false
3  | 1         | false
4  | 2         | false
5  | 2         | true

What i want to achieve

Each company must have one (and only one) primary address. Each time the "MainAddress" column is updated, I need to make sure that this requirement is still met. Scenarios:

• Address record with identifier 2 becomes the main address → MainAddress for address with identifier 1 must be set to false
• MainAddress 1 true false → , true, (doesn ' )
• 2 3 → MainAddress ( , ) true, MainAddress false
• MainAddress = true → ( , , )
• MainAddress = true → MainAddress false

, , , .

, Insert, Update, Delete, CompanyID :

INSERT INTO #Temp_SetMainAddress
SELECT DISTINCT "CompanyID" FROM inserted;

DECLARE reccursor CURSOR STATIC FORWARD_ONLY READ_ONLY FOR
SELECT ID
FROM #Temp_SetMainAddress

5 .

, , ? ( 7000 , 20 ). , -, , .

Answer 1

, 6 , , , . , .

, :

• , , , , , ... : . - . Addresses: FireTrigger. , . .

• PK Addresses AddressesID. ID .

• , , . , AddressID , .

• . , , .

:

CREATE TRIGGER Addresses_OnInsertUpdate
    ON Addresses
    AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @tmp TABLE (CompanyID int, MainAddressID int)

    ;WITH cte1 AS
    (
        SELECT  AddressID, CompanyID,
                ROW_NUMBER() OVER (PARTITION BY CompanyID ORDER BY AddressID) AS RowNumber
        FROM    inserted
        WHERE   MainAddress = 1
            AND FireTrigger = 1
    )

    INSERT INTO @tmp (CompanyID, MainAddressID)
        SELECT  CompanyID, AddressID
        FROM    cte1
        WHERE   RowNumber = 1

    ;WITH cte2 AS
    (
        SELECT      i.CompanyID
        FROM        inserted    i
        LEFT  JOIN  Addresses   a ON i.CompanyID = a.CompanyID
                                 AND a.MainAddress = 1
        WHERE       i.FireTrigger = 1
        GROUP BY    i.CompanyID
        HAVING      COUNT(a.AddressID) = 0
    ),
    cte3 AS
    (
        SELECT      d.CompanyID, d.AddressID
        FROM        deleted     d
        INNER JOIN  cte2        c2 ON d.CompanyID = c2.CompanyID
        WHERE       d.MainAddress = 1
    ),
    cte4 AS
    (
        SELECT      i.CompanyID, i.AddressID,
                    ROW_NUMBER() OVER (PARTITION BY i.CompanyID ORDER BY i.AddressID) AS RowNumber
        FROM        inserted    i
        INNER JOIN  cte2        c2 ON i.CompanyID = c2.CompanyID
    )

    INSERT INTO @tmp (CompanyID, MainAddressID)
        SELECT  CompanyID, AddressID
        FROM    cte3
        UNION
        SELECT  CompanyID, AddressID
        FROM    cte4
        WHERE   RowNumber = 1


    UPDATE Addresses
        SET         MainAddress = CASE a.AddressID
                                    WHEN MainAddressID THEN 1
                                    ELSE 0
                                  END,
                    FireTrigger = 0
        FROM        Addresses   a
        INNER JOIN  @tmp        tmp ON a.CompanyID = tmp.CompanyID
END

@tmp , AddressID , ​​ .

• cte1 , Main. AddressID .

• cte2 /. : false (. cte3) Main Address = false (. cte4)

• cte3 AddressID .

• cte4 AddressID .

cte1, cte3 cte4 AddressID , /. , FireTrigger true:

INSERT INTO Addresses (..., FireTrigger)
    VALUES (...., 1)

UPDATE Adddresses
    SET ..., FireTrigger = 1
    WHERE ...

CREATE TRIGGER dbo.Addresses_OnDelete
    ON dbo.Addresses
    AFTER DELETE
AS
BEGIN
    ;WITH cte2 AS
    (
        SELECT      d.CompanyID
        FROM        deleted     d
        LEFT JOIN   Addresses   a ON d.CompanyID = a.CompanyID
                                 AND a.MainAddress = 1
        GROUP BY    d.CompanyID
        HAVING      COUNT(a.AddressID) = 0
    ),
    cte3 AS
    (
        SELECT      a.CompanyID, a.AddressID,
                    ROW_NUMBER() OVER (PARTITION BY a.CompanyID ORDER BY a.AddressID) AS RowNumber
        FROM        cte2        c2
        INNER JOIN  Addresses   a   ON c2.CompanyID = a.CompanyID
    )

    UPDATE Addresses
        SET         MainAddress = 1,
                    FireTrigger = 0
        FROM        Addresses   a
        INNER JOIN  cte3        c3 ON a.CompanyID = c3.CompanyID
                                  AND a.AddressID = c3.AddressID
        WHERE       c3.RowNumber = 1
END

, :

• , cte2 ( cte1 - ).
• cte3 AddressID