0

Is it possible to use a trigger to update multiple rows? For example, I have an app that will use a Table Value Param & a SQL Stored Proc to dump anywhere between 15 and 20 rows into a table. Once that data is dumped, I then need a way (I was thinking a trigger) to update the remaining "missing" fields. I thought this approach may work (it is the valid syntax for my update)

Create TRIGGER [dbo].[UpdateFields]
ON  [dbo].[TFData]
AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
UPDATE a
Set 
a.DateOfInjury = b.InjurDate
,a.GameNum = c.GameNum
FROM [dbo].[InjuryReport] a
INNER JOIN [dbo].[GameStats] b
ON a.playnum = b.playnum
INNER JOIN [dbo].[GameSched] c
On b.gamenum = c.gamenum

How would I need to code this trigger to update multiple rows? I assume I would somehow need to join on the virtual table but I am not certain.

1

You are right, there is a virtual table called "inserted" which is what you would need to join on. This table contains all the rows which have been inserted in the original table insert statement. So something like:

CREATE TRIGGER [dbo].[UpdateFields]
ON  [dbo].[TFData]
AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
UPDATE a
Set 
a.DateOfInjury = b.InjurDate
,a.GameNum = c.GameNum
FROM [dbo].[InjuryReport] a
INNER JOIN [dbo].[GameStats] b
ON a.playnum = b.playnum
INNER JOIN [dbo].[GameSched] c
On b.gamenum = c.gamenum
INNER JOIN inserted i
ON b.linkfield = i.linkfield
| improve this answer | |

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.