[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

INSERT Trigger on multiple rows

Posted on 2003-12-01
14
Medium Priority
?
886 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:chrishq
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9849082
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?
 
0
 
LVL 2

Author Comment

by:chrishq
ID: 9849131
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?
0
 
LVL 2

Author Comment

by:chrishq
ID: 9849157
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9849161
INSERT INTO tblInvoiceItem (InvoiceNumber, ItemID, TotalRate, Qty)
SELECT InvoiceNumber, ItemID, TotalRate, Qty
FROM Inserted

in ur Trigger OR u can use Corsor..
0
 
LVL 2

Author Comment

by:chrishq
ID: 9849195
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
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9849214
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
)
0
 
LVL 2

Author Comment

by:chrishq
ID: 9849288
Hi dishanf,

SELECT Inserted.InvoiceNumber... gives the error:

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

Any ideas?
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9849302
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
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9849309
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
0
 
LVL 1

Expert Comment

by:cerdal
ID: 9849403
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.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 9849859
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

 
 
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9849917
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
0
 
LVL 1

Expert Comment

by:cerdal
ID: 9849980
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
0
 
LVL 2

Author Comment

by:chrishq
ID: 9855889
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.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question