[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

INSERT Trigger on multiple rows

Posted on 2003-12-01
14
Medium Priority
?
884 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

650 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