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.