1

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.

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
  • better to SET SET Inventory_Ind_Trees.Abs_Plot_Numb = inserted.Abs_Plot_Numb FROM Inventory_Ind_TreesJOIN direct with inserted - INNER JOIN inserted ON Inventory_Ind_Trees.Invent_Plot_ID = inserted.Invent_Plot_ID – a_vlad Mar 4 '16 at 22:02
  • Thank you for your comments. It makes sense to only update the inserted rows instead of the entire table. The WHERE EXISTS @MaxVernon provided seems to work well but it is only updating the number of rows in the inserted table. Is there a way to update all records in Inventory_Ind_Trees (sample data has 80 records) with the Abs_Plot_Numb values from the Plot_Location table? – Mike Fry Mar 7 '16 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.