I have an INSERT statement in a stored procedure similar to:
INSERT INTO tblInvoice (InvoiceNumber, InvoicePeriod, CustomerID, StartDate)
SELECT @InvoicePeriod + CustomerID AS InvoiceNumber, @InvoicePeriod AS InvoicePeriod, CustomerID, @StartDate AS StartDate
This inserts one row for every customer into tblInvoice. What I want to do now is for each row inserted is add a record to another table using the InvoiceNumber just created ie.
INSERT INTO tblInvoiceItem (InvoiceNumber, ItemID, TotalRate, Qty)
SELECT Inserted.InvoiceNumber, dbo.tblItem.ItemID, dbo.tblItem.TotalRate, dbo.tblItem.Qty WHERE ... some criteria
(This second query also inserts several rows)
I am trying to use an AFTER INSERT trigger on tblInvoice (--If there is a way to do this inside the procedure above without a trigger please let me know--) but i have the problem that the trigger only fires once
How do i now work through each row in the inserted table to run the second query for each new row?