question

mbatchelor55 avatar image
mbatchelor55 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;
updatetriggers
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
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
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom: It worked. I thank you sooooooo very much.
0 Likes 0 ·
Glad I could help. Please mark the answer as accepted so others know.
0 Likes 0 ·
How do I mark it as accepted?
0 Likes 0 ·
JoeHayes avatar image
JoeHayes answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I do not understand what you mean by ' Inserted.id'.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.