Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: Insert multiple rows with a trigger that invoke a function

    Hello, I am having 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 ?
    I am reading regarding cursor.

    Please, thanks in advance for any help,

    Regards, Paul.-

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Replace "update Customer
    set Percentage = dbo.GetPercentage((select Value from inserted))
    where CustomerId = (select CustomerId from inserted)" by
    Code:
    update U
    set U.Percentage = dbo.GetPercentage(Inserted.Value)
    FROM Customer AS U
    	INNER JOIN Inserted ON
    		U.CustomerId = Inserted.CustomerId
    GetPercentage(), is that a User Defined Function?

    But forget the trigger. Have a look at computed columns. I think that is what you really need.
    Last edited by Wim; 01-18-12 at 05:18.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •