asked
Having trouble with a trigger.
I have a trigger on a master table for a specific field that when I update that field I want to populate four (4) other fields on another master table. With this trigger, I am updating all rows in the 2nd master table for these 4 fields. I just want to update the 2nd master table for the ID# that I am on. What am I doing wrong? Here is my trigger. CREATE TRIGGER [dbo].[tr_update_table1_field1] ON [dbo].[table2] AFTER UPDATE AS -- field1 is on table2 if (UPDATE (field1)) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here UPDATE table1 SET table1.UDEF_5A_1 = table3.field1, table1.DEGREE_YR = table3.field1, table1.UDEF_2A_1 = table3.field2, table1.DEGREE_TRM = table3.field2 FROM table1 JOIN table2 ON
table2.ID =
table1.ID AND table1.CUR = 'Y' JOIN table3 B ON table2.field1 BETWEEN B.BEGIN_DTE AND B.END_DTE; END;
Triggers expose special tables called "inserted" and "deleted". You can read more about those [here][1]. For an update trigger, "deleted" contains the updated rows with original values, and "inserted" contains the updated rows with the new values. Rather than referencing table2 in your trigger, you should reference inserted to only get the rows that were updated. I believe this should do what you want: UPDATE t1 SET UDEF_5A_1 = t3.field1 , DEGREE_YR = t3.field1 , UDEF_2A_1 = t3.field2 , DEGREE_TRM = t3.field2 FROM table1 t1 INNER JOIN inserted t2 ON
t2.ID =
t1.ID AND t1.CUR = 'Y' INNER JOIN table3 t3 ON t2.field1 BETWEEN t3.BEGIN_DTE AND t3.END_DTE ; [1]:
https://msdn.microsoft.com/en-us/library/ms191300.aspx
Glad I could help. Please mark the answer as accepted so others know.
If I understand what you're trying to do, the fundamental problem is that you have no where clause. Without a where clause, all records will be updated. where
table2.id =
Inserted.id
21 People are following this question.
