trigger question - multiple rows to insert? 2

[kskinne]

Posting another trigger question - using SQL2005

I have the following trigger that works fine if there is only one row for the trigger to insert:

ALTER TRIGGER [updCommReceived_OtherPricePlans]
ON [dbo].[TransactionEntry]
FOR INSERT
AS

INSERT INTO c_CommReceived(StoreID, TransactionNumber, TransID, Commission, QtyBonus, Coop)
SELECT INSERTED.StoreID, INSERTED.TransactionNumber, INSERTED.ID,
(SELECT c_CommRates.Commission FROM c_CommRates WHERE c_CommRates.DepartmentID = (SELECT DepartmentID FROM Item WHERE Item.ID = INSERTED.ItemID)
AND (SELECT EquivalentValue FROM c_CellPlans WHERE c_CellPlans.ItemID = INSERTED.ItemID) >= LowerBound
AND (SELECT EquivalentValue FROM c_CellPlans WHERE c_CellPlans.ItemID = INSERTED.ItemID) <= Upperbound
) * INSERTED.Quantity,
5 * INSERTED.Quantity, 0 * INSERTED.Quantity
FROM INSERTED
INNER JOIN Item ON INSERTED.ItemID = Item.ID
WHERE Item.DepartmentID IN (65, 67)

However if there are more than one records in the INSERTED table, requiring more than one record to be inserted into the c_commreceived table, the trigger fails. Any idea as to why this is not working?

Thank you,
Kevin

 

[markros]

When you use subqueries as the field list, it must return 1 or 0 records. In your case it will return more than 1 record, so that's why it's failing.

What is the idea behind this code and can you simply join with the Inserted table instead of using subqueries?

PluralSight Learning Library

 

[gmmastros]

Here's a wild guess:

INSERT	
INTO   c_CommReceived(StoreID, TransactionNumber, TransID, Commission, QtyBonus, Coop)
SELECT INSERTED.StoreID, 
       INSERTED.TransactionNumber, 
       INSERTED.ID, 
       c_CommRates.Commision * INSERTED.Quantity,
       5 * INSERTED.Quantity, 
       0 * INSERTED.Quantity
FROM   INSERTED
       INNER JOIN Item 
         ON INSERTED.ItemID = Item.ID
       INNER JOIN c_CommRates
         On Item.DepartmentId = c_CommRates.DepartmentId
       INNER JOIN c_CellPlans
         On Inserted.ItemID = c_CellPlans.ItemId
         And c_CellPlans Between LowerBound And UpperBound
WHERE  Item.DepartmentID IN (65, 67)

I'm a little confused about: 0 * INSERTED.Quantity With my current understanding of math, anything multiplied by 0 is always 0.

Also, please understand that this is a big guess on my part. I give it about a 25% chance of actually working.

To increase the chances of getting a working query, I'd need to see some data from all rows involved as well as some sample data that is being inserted in to the table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kskinne]

Thank you - I will try re-writing this to join my inserted table with the other table, that should take care of it. I will try this and probably post back next week.

The inserted table is a transactionentry table where line items on a transaction get written to the table as individual records. I need the trigger to calculate a commission when necessary (only on certain item departmentID's) and write this commission data to the c_commreceived table. I have a front end built for this table that is used for tracking the commissions and marking when they are paid, adjusting them, etc.

Thanks a ton for your help, I really appreciate it.

Kevin

 

[kskinne]

I replaced my original INSERT statement with this:

INSERT INTO c_CommReceived(StoreID, TransactionNumber, TransID, Commission, QtyBonus, Coop)
SELECT INSERTED.StoreID,
INSERTED.TransactionNumber, INSERTED.ID,
c_CommRates.Commission * INSERTED.Quantity,
5 * INSERTED.Quantity, 20 * INSERTED.Quantity
FROM INSERTED
INNER JOIN Item ON INSERTED.ItemID = Item.ID
INNER JOIN c_CellPlans ON c_CellPlans.ItemID = INSERTED.ItemID
INNER JOIN c_CommRates ON Item.DepartmentID = c_CommRates.DepartmentID
WHERE Item.DepartmentID = 64
AND c_CellPlans.EquivalentValue >= c_CommRates.LowerBound
AND c_CellPlans.EquivalentValue <= c_CommRates.UpperBound

And now is allowing multiple inserts - thank you for your help with this.

Kevin