Sql-server – Two Triggers affect the same row

Regarding your question about how to have triggers from two independent tables (A, and B) insert rows into a third table (C), you can certainly do that. Presumably both A and B have a column that identifies the specific row in C that needs to be updated.

To prove this, I've created the two source tables A and B, with a trigger on each table, that inserts into the C table.

CREATE TABLE dbo.A
(
    RowID INT NOT NULL
        CONSTRAINT PK_A
        PRIMARY KEY CLUSTERED
    , SomeDataA VARCHAR(386) NOT NULL
);

CREATE TABLE dbo.B
(
    RowID INT NOT NULL
        CONSTRAINT PK_B
        PRIMARY KEY CLUSTERED
    , SomeDataB VARCHAR(386) NOT NULL
);

CREATE TABLE dbo.C
(
    RowID INT NOT NULL
        CONSTRAINT PK_C
        PRIMARY KEY CLUSTERED
    , SomeDataA VARCHAR(386) NULL
    , SomeDataB VARCHAR(386) NULL
);
GO

CREATE TRIGGER dbo.A_Update_C
ON dbo.A
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @t TABLE
    (
        RowID INT NOT NULL
    );
    UPDATE dbo.C 
    SET SomeDataA = i.SomeDataA
    OUTPUT i.RowID INTO @t (RowID)
    FROM inserted i
    INNER JOIN dbo.C c ON i.RowID = c.RowID;

    INSERT INTO dbo.C (RowID, SomeDataA)
    SELECT i.RowID
        , i.SomeDataA
    FROM inserted i
    WHERE NOT EXISTS (
        SELECT 1
        FROM @t t
        WHERE t.RowID = i.RowID
        );
END
GO

CREATE TRIGGER dbo.B_Update_C
ON dbo.B
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @t TABLE
    (
        RowID INT NOT NULL
    );
    UPDATE dbo.C 
    SET SomeDataB = i.SomeDataB
    OUTPUT i.RowID INTO @t (RowID)
    FROM inserted i
    INNER JOIN dbo.C c ON i.RowID = c.RowID;

    INSERT INTO dbo.C (RowID, SomeDataB)
    SELECT i.RowID
        , i.SomeDataB
    FROM inserted i
    WHERE NOT EXISTS (
        SELECT 1
        FROM @t t
        WHERE t.RowID = i.RowID
        );
END
GO

Here we insert test data into A and B, then show the rows in C:

INSERT INTO dbo.A (RowID, SomeDataA)
VALUES (1, 'x');

INSERT INTO dbo.B (RowID, SomeDataB)
VALUES (1, 'y');

SELECT *
FROM dbo.C;

The rows in C are:

The code relies on the RowID value being identical in both A and B; without some commonality, it is impossible to know which rows in C need to be updated.

The triggers above only inserts new rows into C if they don't already exist by using the OUTPUT clause to insert the RowID values into a temporary table which is subsequently used to prevent those rows from being inserted into the C table. Only rows that don't already exist in the @t table variable get inserted into C.

If there is a high degree of concurrent activity against A and B, with different processes potentially updating the same rows in those tables, it may be possible to attempt to insert duplicate rows into C. This would fail, causing the entire transaction to fail, rolling back the changes made by both the trigger in C, as well as the changes made to either A or B. The code above does not account for this edge-case, and in fact has no error checking at all.

The below answer and code is regarding the first "version" of your question, which was "can multiple triggers write to the same row".

You can very easily test that using:

USE tempdb;

CREATE TABLE dbo.TriggerTest
(
    TriggerTest_ID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData VARCHAR(255)
);
GO
CREATE TRIGGER dbo.TriggerTest1 ON dbo.TriggerTest
FOR INSERT
AS
BEGIN
    UPDATE dbo.TriggerTest
    SET SomeData = COALESCE(tt.SomeData, '') + 'TriggerTest1'
    FROM dbo.TriggerTest tt
        INNER JOIN inserted i ON tt.TriggerTest_ID = i.TriggerTest_ID;
END
GO
CREATE TRIGGER dbo.TriggerTest2 ON dbo.TriggerTest
FOR INSERT
AS
BEGIN
    UPDATE dbo.TriggerTest
    SET SomeData = COALESCE(tt.SomeData, '') + 'TriggerTest2'
    FROM dbo.TriggerTest tt
        INNER JOIN inserted i ON tt.TriggerTest_ID = i.TriggerTest_ID;
END
GO
TRUNCATE TABLE dbo.TriggerTest;
INSERT INTO dbo.TriggerTest DEFAULT VALUES;
SELECT *
FROM dbo.TriggerTest;

If you run this, you'll see the following results:

This clearly proves that two separate FOR INSERT triggers can, and in fact do, update rows independently of each other. If both triggers are coded to affect the inserted rows, then that is what they will do.

Unless you specify the trigger order using the sys.sp_settriggerorder stored procedure, it is not guaranteed that the trigger created first will fire first. If the logic contained in the these triggers depends on a certain logical firing order, you must ensure you specify the order. For instance:

--make TriggerTest2 run first:
EXEC sys.sp_settriggerorder 
    @triggername = 'dbo.TriggerTest2'
    , @order = 'First'
    , @stmttype = 'INSERT';

--set TriggerTest1 to run in any order.  The @order parameter could be 
--  changed to 'Last' to ensure it runs after all other triggers on 
--  that table
EXEC sys.sp_settriggerorder 
    @triggername = 'dbo.TriggerTest1'
    , @order = 'None'
    , @stmttype = 'INSERT';

Also of note, you should realize triggers fire once for any given INSERT statement. Take for instance:

TRUNCATE TABLE dbo.TriggerTest;

INSERT INTO dbo.TriggerTest (SomeData)
SELECT o.name
FROM sys.objects o;

SELECT *
FROM dbo.TriggerTest;

In my tempdb, this created 101 rows, a sample of which is:

I coded my triggers to properly handle this by using a set-based approach to updating the underlying table with a JOIN to the special table, inserted, which is a system-created virtual table containing the rows to be inserted into the table.