INSERT Trigger on multiple rows

Hi,

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
FROM dbo.tblCustomer

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?

Thanks
Chris

LVL 2
chrishqAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
the trigerr fires once for "parent SQL Statement" affecting the table

the inserted and deleted tables within the trigger contain the details of all the affected rows...

just use another Insert statement...

insert into othertable
 select x,y,z from INSERTED
   ....

what am i missing?
 
chrishqAuthor Commented:
I would like to be able to go through a few different steps for each of the inserted rows and I am not sure how to loop through each row in the INSERTED table, do i need a cursor or is there a simpler method?
chrishqAuthor Commented:
If I use something like...

insert into othertable
 select x,y,z from INSERTED

I want the value y to represent multiple rows returned by a subquery so if i try that format i get an error about multiple rows being returned.

In other words for every record put into tblInvoice, i want 0 or more rows to be put into tblInvoiceItem
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Dishan FernandoSoftware Engineer / DBACommented:
INSERT INTO tblInvoiceItem (InvoiceNumber, ItemID, TotalRate, Qty)
SELECT InvoiceNumber, ItemID, TotalRate, Qty
FROM Inserted

in ur Trigger OR u can use Corsor..
chrishqAuthor Commented:
This is more along the lines of what i want to do but does not work...

INSERT INTO tblInvoiceItem (InvoiceNumber, ItemID, TotalRate, Qty)
SELECT Inserted.InvoiceNumber, dbo.tblItem.ItemID, dbo.tblItem.TotalRate, dbo.tblItem.Qty
FROM tblItem
WHERE dbo.tblItem.CustomerID = Inserted.CustomerID

I just want to use some of the values -- row by row -- in a subquery rather than just copy the contents of tblInvoice into tblInvoiceItem

Thanks

Chris
Dishan FernandoSoftware Engineer / DBACommented:
try this....

INSERT INTO tblInvoiceItem (InvoiceNumber, ItemID, TotalRate, Qty)
SELECT Inserted.InvoiceNumber, dbo.tblItem.ItemID, dbo.tblItem.TotalRate, dbo.tblItem.Qty
FROM tblItem
WHERE dbo.tblItem.CustomerID IN
(
SELECT CustomerID
FROM  Inserted
WHERE CustomerID IS NOT NULL
)
chrishqAuthor Commented:
Hi dishanf,

SELECT Inserted.InvoiceNumber... gives the error:

"...column prefix 'Inserted' does not match with a table name ...."

Any ideas?
Dishan FernandoSoftware Engineer / DBACommented:
hey.. I just see it.

SELECT Inserted.InvoiceNumber, dbo.tblItem.ItemID, dbo.tblItem.TotalRate, dbo.tblItem.Qty
FROM tblItem
this SQL is wrong !  

INSERT INTO tblInvoiceItem (InvoiceNumber, ItemID, TotalRate, Qty)
SELECT Ins.InvoiceNumber, I.ItemID, I.TotalRate, I.Qty
FROM tblItem I INNER JOIN Inserted Ins
ON I.CustomerID = Ins.CustomerID
Dishan FernandoSoftware Engineer / DBACommented:
is this doent work. Pls send the Details. Waht is the table that triggers fires.? Ithe is the Table That you want to Insert data. Like that
cerdalretiredCommented:
Here's a trigger I use to ensure all records in a second table that correspond to those in the main table are deleted
when I delete several at once from the main table:

CREATE TRIGGER tr_deletefrom_tarif ON dbo.types
FOR DELETE
AS
DECLARE @tyserial int
/* une seule fonction:
   supprimer de la table tarif tout tarif pour chaque type enlevé
*/
DECLARE delcursor CURSOR FOR SELECT tyserial FROM deleted FOR READ ONLY
OPEN delcursor
      
FETCH NEXT FROM delcursor INTO @tyserial
WHILE @@FETCH_STATUS <> -1
BEGIN
  IF EXISTS (SELECT * FROM tarif WHERE tatypserial = @tyserial)
  BEGIN
    DELETE FROM tarif WHERE tatypserial = @tyserial
  END
  FETCH NEXT FROM delcursor INTO @tyserial
END
CLOSE delcursor
DEALLOCATE delcursor
GO

You can probably use a similar cursor for your problem.

I got the technique from the online books installed with SQL Server 6.5.

Chris Erdal.
LowfatspreadCommented:
Yes you can loop through them...

don't use a cursor...

i assume you have an appropriate Key...

Declare @Key int
Declare @Othercolumn AppropiateDatatype


while exists (select 'y' from Inserted where tablekey > @key )
begin
 select top 1 @othercolumn=columnname , @key=key from inserted where tablekey > @key
    order by tablekey
 do what you want....
end

 
 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HilaireCommented:
Nice bit of coding Lowfatspread,

Just a lacking line :

set @key=0      before the while
if you don't set a value for @key, default value is null, so tablekey > @key returns false whatever the value of inserted.tablekey

Just my two cents to (don't accept this comment as an answer)

Hilaire
cerdalretiredCommented:
I would have thought a cursor was more efficient.

A cursor executes once only, before the loop, whereas the "while exists" executes 2 new scripts on "Inserted" for every loop. If there is a large number of records in "inserted" then this could increase the execution time / cpu load significantly, no?

Another thing to keep in mind is that in 5 years time someone else is perhaps going to need to modify something here, and imho using a cursor is more self-evident (to someone perhaps with less experience) than nifty coding...

Chris
chrishqAuthor Commented:
Thanks heaps for all the help everyone. I knew there had to be another way other than using a cursor so points to Lowfatspread that did the trick.

Last thing does anyone no for sure if the loop or the cursor is more efficent? My first procedure inserts about a thousand records at a time into the first table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.