SQL Server Trigger does not update all table records

Question

This is my first question so please go easy on me. We are using Access 2013 as a front-end and SQL Server 2012 with MS SQL Server Management Studio 11.0.2100.60 backend with ODBC connections.

I am trying to write a series of triggers to update a couple of different tables. When I paste the data manually in Access (our front-end) the trigger only updates 71 of 80 records of the sample data. If I disable all triggers and pull out only the query portion all of the records (80) are updated. Why is my trigger not updating the entire table?

Here is the trigger:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
   ON  [dbo].[Plot_Location]
   AFTER INSERT
AS 
    if @@rowcount = 0
RETURN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID

The portion I run as a stand alone query that updates the entire table is here:

UPDATE Inventory_Ind_Trees
SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
FROM Plot_Location
WHERE Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID

The records that are not updated are left as NULL. I am able to change them manually but I want this to be an automatic process.

Any suggestions where to look for the answer, or any help pointing me in the right direction would be greatly appreciated.

I only want to update the new records that have been put into the Inventory_Ind_Trees table with the corresponding Abs_Plot_Numb values from the Plot_Location table, but I was not quite sure how to do that.

Answer 1

Currently, your trigger is updating all rows. Modify it to use the inserted virtual table, something like this:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
ON [dbo].[Plot_Location]
AFTER INSERT
AS 
BEGIN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE EXISTS (
            SELECT 1 
            FROM inserted i 
            WHERE i.Invent_Plot_ID = Plot_Location.Invent_Plot_ID
        )
        AND Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID;
END

This may not be exactly correct; it's hard to tell exactly where to join the inserted virtual table with your other tables. Essentially, the idea is the inserted virtual table only contains rows that were either inserted or updated in the Plot_Location table.

You shouldn't check the @@ROWCOUNT system function at the start. It may not reflect the rowcount you think it does. Instead you should select the count of rows in the inserted table. Like this:

DECLARE @rc INT;
SELECT @rc = COUNT(1)
FROM inserted;

IF @rc > 0
BEGIN
    --body of trigger  goes here.
END