Insert Trigger and Bulk Insert

I am using Bulk Insert to insert multiple rows into a table (Invoice_Lines_Temp). I need to update/insert rows in another table (Invoice_Lines) as these rows are inserted into Invoice_Lines_Temp. As I understand, the trigger is only fired once for the insert so only one row is affected in Invoice_Lines, and this is what I'm seeing.

How would someone modify this trigger to update/insert the Invoice_Line table for each record inserted into Invoice_Line_Temp?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER TRIGGER [dbo].[Invoice_Line_Temp_Insert]

on [dbo].[Invoice_Line_Temp]

for Insert

As

Declare @.Transaction_ID int,

@.Line_Number int,

@.Item_Desc varchar(32),

@.Invoice_Date datetime,

@.Group_Number int,

@.Item_Code varchar(8),

@.Quantity_Sold decimal,

@.Section_Number int,

@.LastUpdate datetime

Select @.Transaction_ID = inserted.Transaction_ID,

@.Line_Number = inserted.Line_Number,

@.Item_Desc = Inserted.Item_Desc,

@.Invoice_Date = inserted.Invoice_Date,

@.Group_Number = inserted.Group_Number,

@.Item_Code = inserted.Item_Code,

@.Quantity_Sold = inserted.Quantity_Sold,

@.Section_Number = inserted.Section_Number,

@.LastUpdate = inserted.LastUpdate

from inserted

IF Exists

(Select dbo.invoice_line.transaction_id, dbo.invoice_line.line_number

from dbo.invoice_line

where dbo.invoice_line.transaction_id = @.Transaction_id

and dbo.invoice_line.Line_Number = @.Line_Number)

Begin

update dbo.invoice_line

set Item_Desc = @.Item_Desc,

Invoice_Date = @.Invoice_Date,

Group_Number = @.Group_Number,

Item_Code = @.Item_Code,

Quantity_Sold = @.Quantity_Sold,

Section_Number = @.Section_Number,

LastUpdate = @.LastUpdate

where dbo.invoice_line.transaction_id = @.Transaction_id

and dbo.invoice_line.Line_Number = @.Line_Number

end

ELSE

Begin

INSERT INTO dbo.Invoice_Line

(InvoiceLineId,Transaction_ID,Line_Number,Item_Desc,Invoice_Date,

Group_Number,Item_Code,Quantity_Sold,Section_Number,LastUpdate)

VALUES

(newid(),@.Transaction_ID,@.Line_Number,@.Item_Desc,@.Invoice_Date,

@.Group_Number,@.Item_Code,@.Quantity_Sold,@.Section_Number,@.LastUpdate)

end

Try this:

Code Snippet

ALTER TRIGGER [dbo].[Invoice_Line_Temp_Insert]

on [dbo].[Invoice_Line_Temp]

for Insert

As

BEGIN

UPDATE il

SET Item_Desc = i.Item_Desc,

Invoice_Date = i.Invoice_Date,

Group_Number = i.Group_Number,

Item_Code = i.Item_Code,

Quantity_Sold = i.Quantity_Sold,

Section_Number = i.Section_Number,

LastUpdate = i.LastUpdate

FROM dbo.invoice_line il

INNER JOIN inserted

ON il.transaction_id = i.Transaction_id

AND il.Line_Number = i.Line_Number

INSERT INTO dbo.Invoice_Line

(InvoiceLineId,Transaction_ID,Line_Number,Item_Desc,Invoice_Date,

Group_Number,Item_Code,Quantity_Sold,Section_Number,LastUpdate)

SELECT newid(), i.Transaction_id, i.Line_number, i.Item_Desc, i.Invoice_Date,

i.GroupNumber, i.Item_Code, i.Quantity_Sold, i.Section_Number, i.LastUpdate

FROM inserted

WHERE NOT EXISTS

( SELECT * FROM dbo.Invoice_Line il

WHERE il.transaction_id = i.Transaction_id

AND il.Line_Number = i.Line_Number)

END

|||Worked great! More elegant as well. thanks...