Solved

INSERT Trigger on multiple rows

Posted on 2003-12-01
14
870 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
 
LVL 8

Expert Comment

by:dishanf
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:dishanf
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 8

Expert Comment

by:dishanf
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:dishanf
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 125 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now