Triggers data retention in inserted table and combination of update and insert on one column

I have a table in SQL Server which houses snapshots of all data to track changes in status of the source table tbl_D_project. If this status changes or a new record is added in tbl_D_project, a trigger should start and the line will be copied to the snapshot table.

I wrote two triggers based on another question I found here. One trigger for the update and one for the insert. My questions are:

1. Is there a more efficient way? Can I somehow combine the two in one, so After Insert, Update?

2. Multiple users are submitting data at the same time. How long does the data stay in the inserted table? Is there a possibility that if two users submit data, the trigger will be fired twice, so the lines will be copied twice to the snapshot table?

After update trigger:

ALTER TRIGGER [dbo].[tr_copysnapshot_update]
ON [CDB].[dbo].[tbl_D_project]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON
  
    INSERT INTO cbdbpc.dbo.[Snapshot_tbl_D_project]
        (Snapshot_date, Opp_ID, Reference_ID, Project_ID, Project_opportunity, [State], [Status])
        SELECT
            GETDATE(), S.Opp_ID, S.Reference_ID, S.Project_ID, 
            S.Project_opportunity, S.[State], S.[Status]
        FROM 
            cbdbpc.dbo.[tbl_D_project] S
        INNER JOIN 
            Inserted I ON s.opp_id = I.Opp_ID
        INNER JOIN 
            Deleted D ON s.opp_ID = D.Opp_ID
        WHERE 
            D.[Status] <> I.[Status]
END

After insert trigger:

ALTER TRIGGER [dbo].[tr_copysnapshot_insert]
ON [CBD].[dbo].[tbl_D_project]
AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON
  
    INSERT INTO cbdbpc.dbo.[Snapshot_tbl_D_project]
        (Snapshot_date, Opp_ID, Reference_ID, Project_ID, 
         Project_opportunity, [State], [Status])
        SELECT
            GETDATE(), S.Opp_ID, S.Reference_ID, S.Project_ID, 
            S.Project_opportunity, S.[State], S.[Status]
        FROM 
            cbdbpc.dbo.[tbl_D_project] S
        INNER JOIN 
            Inserted I ON s.opp_id = I.Opp_ID
        WHERE 
            S.[Opp_ID] = I.[Opp_ID]
END 

SQL update trigger only when column is modified

Answer

Yes you can combine the triggers, just use a LEFT JOIN to the memory resident table deleted, and change your where clause so that you only add a row if the status changes (Update) or there is no record in deleted (insert), i.e.

INSERT INTO cbdbpc.dbo.[Snapshot_tbl_D_project]
    (Snapshot_date, Opp_ID, Reference_ID, Project_ID, Project_opportunity, [State], [Status])
SELECT
    GETDATE(), S.Opp_ID, S.Reference_ID, S.Project_ID, 
    S.Project_opportunity, S.[State], S.[Status]
FROM 
    cbdbpc.dbo.[tbl_D_project] S
INNER JOIN 
    Inserted I ON s.opp_id = I.Opp_ID
LEFT JOIN 
    Deleted D ON s.opp_ID = D.Opp_ID
WHERE 
    (D.[Status] <> I.[Status] OR d.Opp_ID IS NULL)

For your second question, what you are describing is not possible, two users can’t do the same update at the same time, even if it is just micro seconds apart it would still do one then the other (each would have it’s own inserted/deleted table), so yes this would always fire the trigger twice.

If both users do the same update, then your where clause stipulating that the status must have changed would prevent a second insert. If you wanted to extend this to capture records when other columns have changed, but not if nothing has changed, you could use the following WHERE clause:

WHERE NOT EXISTS (SELECT i.* INTERSECT SELECT d.*)