SQL Server trigger failing for row inserts in quick succession

Question

I have looked around on SO and found many similar questions:

SQL Server A trigger to work on multiple row inserts

SQL trigger multiple insert update

Trigger to handle multiple row inserts and updates

update multiple rows with trigger after insert (sql server)

Trigger not working when inserting multiple records

But I am still having issues with my trigger to update multiple rows when Inserting multiple rows into a table.

Outline of code I have a Reservation table which has a ReservationID and TourComponentID columns respectively. When I insert into the reservations table I have the following trigger to update the TourComponent table with the ReservationID from the row just inserted into the reservation table with matching TourComponentID:

CREATE TRIGGER [TR_Reservation_CurrentReservation] ON [Reservation] AFTER INSERT AS
UPDATE tc 
    SET tc.[CurrentReservationId] = I.ReservationID   
    FROM [tour].[TourComponent] tc 
    JOIN INSERTED I on I.TourComponentID = tc.TourComponentID
End

This trigger works perfectly when updating one tourComponent to have a new reservation (inserting one row into the reservation table). However if I try update multiple tour components (inserting multiple rows into the reservation table to update multiple rows in the TourComponent table) only the first tour component gets updated, any rows.

Other answers and research has shown me that

Triggers are NOT executed once per row but rather as a set based operation so executed only ONCE for the entire DML operation. So you need to treat it like any other update date with join statement. So I would have expected my joining on the INSERTED table to have handled multiple rows or have I misunderstood this?

Interestingly if I log the trigger variables for TourComponentID, ReservationID and INSERTED rowcount to a temp table foo I can see two records are inserted into my temp table, each with a rowcount of 1.

Using sql profiler to catch the actual sql executed at runtime and running this manually against the database I get two rows updated as desired. It is only when using Entity Framework to update the database ie running the application do I find only one row is updated.

I have tried logging the values to a table FOO in the trigger

INSERT INTO FOO (TourComponentID, ReservationID, Rowcounts )
    SELECT i.TourComponentID, I.ReservationID, 1  --@ReservationId 
    FROM
    INSERTED I

This logs two rows with a rowcount of 1 each time and the correct tourcomponentsID and reservationID but the TourComponent table still only has one row updated.

Any suggestions greatly appreciated.

UPDATE

Tour component ID's are passed as strings in an Ajax post to the MVC Action where tour component models are populated and then passed to be updated one at a time in the code

public void UpdateTourComponents(IEnumerable<TourComponent> tourComponents)
{
    foreach (var tourComponent in tourComponents)
    {
        UpdateTourComponent(tourComponent);
    }
}

here is the call to UpdateTourComponent

public int UpdateTourComponent(TourComponent tourComponent)
{
    return TourComponentRepository.Update(tourComponent);
}

and the final call to Update

public virtual int Update(TObject TObject)
{
    Dictionary<string, List<string>> newChildKeys;
    return Update(TObject, null, out newChildKeys);
}

So the Inserts are happening one at a time, hence my trigger is being called once per TourComponent. This is why when I count the @@Rowcount in INSERTED and log to Foo I get value of 1. When I run the inserts manually I get the correct expected results so I would agree with @Slava Murygin tests that the issue is probably not with the trigger itself. I thought it might be a speed issue if we are firing the requests one after the other so I put a wait in the trigger and in the code but this did not fix it.

Update 2

I have used a sql profiler to capture the sql that is run when only the first insert triggers work.

Interestingly when the EXACT same sql is then run in SQL Management Studio the trigger works as expected and both tour components are updated with the reservation id.

Worth mentioning also that all constraints have been removed off all tables.

Any other suggestions what might be causing this issue?

Answer 1

You have different problem than that particular trigger. Try to look at the table name you are updating "[tour].[TourComponent]" or "[dbo].[TourComponent]". I've tried your trigger and it perfectly works:

use TestDB
GO
IF object_id('Reservation') is not null DROP TABLE Reservation;
GO
IF object_id('TourComponent') is not null DROP TABLE TourComponent;
GO
CREATE TABLE Reservation (
    ReservationID INT IDENTITY(1,1),
    TourComponentID INT
);
GO
CREATE TABLE TourComponent (
    CurrentReservationId INT,
    TourComponentID INT
);
GO
CREATE TRIGGER [TR_Reservation_CurrentReservation] ON [Reservation] AFTER INSERT AS
UPDATE tc 
    SET tc.[CurrentReservationId] = I.ReservationID   
    FROM [TourComponent] tc 
    JOIN INSERTED I on I.TourComponentID = tc.TourComponentID
GO
INSERT INTO TourComponent(TourComponentID)
VALUES (1),(2),(3),(4),(5),(6)
GO
INSERT INTO Reservation(TourComponentID)
VALUES (1),(2),(3),(4),(5),(6)
GO
SELECT * FROM Reservation
SELECT * FROM TourComponent

Answer 2

So the underlying problem was down to Entity Framework.

this.Property(t => t.CurrentReservationId).HasColumnName("CurrentReservationId");

Is one property for the SQL Data access layer. This was being cached and was causing the data being read out of the db to not be the latest current, thus if we have an insert in the Reservations table the second insert will be overwritten by the cached values which in my case were NULL.

Changing the line to this resolves the problem and makes the trigger work as expected.

this.Property(t => t.CurrentReservationId).HasColumnName("CurrentReservationId").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

See more info on HasDatabaseGeneratedOption