insert multiple rows with a trigger that invoke a function
-
Hello, I need some help with the following problem
Multiple rows to insert:
---------------------
insert into Customer(CustomerId,Name,Value)
select CustomerId,Name,Value
from CustomerTemp
Trigger in Customer table that invoke a function:
---------------------------------------------
alter TRIGGER [dbo].[Calculation] ON [dbo].[Customer]
AFTER INSERT
AS
update Customer
set Percentage = dbo.GetPercentage((select Value from inserted))
where CustomerId = (select CustomerId from inserted)
I'm getting the error for the multiple row.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Is there a way to let me insert multiple rows, using the trigger that invoke a function ?
Please, thanks in advance for any help,
Regards, Paul.-
-
Thank you for you response.
I don't understand how can I resolve this problem with a top ?
Could you explain me something else.
Thanks!!
-
the sql script like this:
ALTER TRIGGER [dbo].[Calculation] ON [dbo].[Customer]
AFTER INSERT
AS
DECLARE @CustomerId INT,@VALUE INT
DECLARE CUR_CUSTOMER CURSOR READ_ONLY FOR
SELECT CustomerId,Value FROM INSERTED
OPEN CUR_CUSTOMER
FETCH NEXT FROM CUR_CUSTOMER INTO @CustomerId,@VALUE
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Customer
SET Percentage = dbo.GetPercentage(@VALUE)
WHERE CustomerId = @CustomerId
FETCH NEXT FROM CUR_CUSTOMER INTO @CustomerId,@VALUE
END;
CLOSE CUR_CUSTOMER
DEALLOCATE CUR_CUSTOMER
-
Hi Eric !!!, thank you very much for your time and all your help.
Paul 🙂
-
A cursor is not the way to go.
UPDATECustomerSETPercentage = i.Percentage
FROMCustomer u
JOIN(
SELECTCustomerID, dbo.GetPercentage(Value) AS Percentage
FROMInserted
) i
ONi.CustomerId = u.CustomerId
The "i" subquery gets one row per record in the Inserted table, with the CustomerId and the Percentage from the function. Then the outer query applies that back to the Customer table based on the ID.
-
Well, a cursor MAY not be the way to go; it depends on the complexity of the function. If it is a large or complex function, you may see a performance degradation when inserting large numbers of rows. If you have a quick function and/or are inserting/updating a few rows at a time, the inline call should work must faster than the cursor. Your best bet is to try the in-line code, then switch to a cursor if you notice performance problems attributable to the function in the trigger.
-
Try this:
ALTER TRIGGER [dbo].[Calculation] ON [dbo].[Customer]
AFTER INSERT
AS
BEGIN
-- Don't forget this line, or applications may break
SET NOCOUNT ON;
UPDATE CUST
SET Percentage = dbo.GetPercentage(INSERTED.Value)
FROM Customer CUST
INNER JOIN INSERTED
ON CUST.CustomerId = INSERTED.CustomerId
END
Avoid CURSORS whenever possible (which means almost always): they are slow, consume a lot of memory and introduce unnecessary locking.
Use set-base code instead, like the one I posted. Don't tell the database HOW to do it, just tell it WHAT to do!
Hope this helps
Gianluca
-- Gianluca Sartori
-
Eric Hu²º¹² (1/17/2012)
It is obvious syntax error.you can use Top 1...
That would update a single row. I'm not sure this is what the OP is after.
-- Gianluca Sartori
-
Eric Hu²º¹² (1/17/2012)
you can use 'CURSOR' to Solve this problem.Obviously you can, but I would never recommend it, unless proven to be the only possible solution.
Set-based code outperforms cursors in the vast majority of cases.
-- Gianluca Sartori
-
fahey.jonathan (1/17/2012)
Well, a cursor MAY not be the way to go; it depends on the complexity of the function. If it is a large or complex function, you may see a performance degradation when inserting large numbers of rows. If you have a quick function and/or are inserting/updating a few rows at a time, the inline call should work must faster than the cursor. Your best bet is to try the in-line code, then switch to a cursor if you notice performance problems attributable to the function in the trigger.Agreed. Also, I would turn the function from scalar to ITVF, in order to let the optimizer merge the execution plans. Scalar functions get invoked for each row and if they have to hit the database to retrieve data you may get performance issues.
-- Gianluca Sartori
-
If you can change the INSERT, you don't need the trigger at all
INSERT INTO Customer(CustomerId,Name,Value,Percentage)SELECT CustomerId,Name,Value,dbo.GetPercentage(Value)
FROM CustomerTemp
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark-101232 (1/18/2012)
If you can change the INSERT, you don't need the trigger at allINSERT INTO Customer(CustomerId,Name,Value,Percentage)SELECT CustomerId,Name,Value,dbo.GetPercentage(Value)
FROM CustomerTemp
BWAAA-HAAA!!!! MARK FOR PRESIDENT! :-D;-)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Mark-101232 (1/18/2012)
If you can change the INSERT, you don't need the trigger at allINSERT INTO Customer(CustomerId,Name,Value,Percentage)SELECT CustomerId,Name,Value,dbo.GetPercentage(Value)
FROM CustomerTemp
Solving the problem at the source - I like it!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply