SQL Server Trigger to handle multiple update rows. sub query returning more then one value

I got this trigger working for single row insert or update, but when I try to update multiple rows at once, it gives error that sub query returns more then one value.

For example

update paymentdata
set stat=1 

Trigger code is here

USE [AGP]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[OnpaymentUpdate]
   ON  [dbo].[paymentData]
 AFTER UPDATE --operations you want trigger to fire on
AS 

BEGIN   


    SET NOCOUNT ON;

    DECLARE @customerID NCHAR(50),  @lastpaymentDate DATETIME, @stat nchar(50), @month int;

    SET @customerID= (SELECT customerID FROM inserted)  --table inserted contains inserted rows (or new updated rows)

    SET @stat= (SELECT stat FROM inserted) --table inserted contains inserted rows (or new updated rows)

    set @lastpaymentDate =  (SELECT MAX(paymentDate) FROM paymentReceipt where customerID=@customerID)  


SET @month= (SELECT DATEDIFF(MONTH,  @lastpaymentDate,GETDATE()))
 DECLARE @balance BIGINT

    SET @balance = 
            (
                SELECT (totalprice-(paidAmount+concession)) 
                FROM paymentData
                WHERE customerID = @customerID
            )

    UPDATE PaymentData
        SET balanceAmount = @balance ,
          lastpaymentDate=@lastpaymentDate
    WHERE customerID = @customerID


if (@month >=2  and @stat!='Cancel' and @stat!='Refund' And @stat!='Refunded' and @stat!='Transfered' and @stat!='Transfer')
Begin

IF  (@month <2 and @stat='Defaulter')
 SET @stat='Regular'
 IF (@balance<=0)
 SET @stat='Payment Completed'
 else
 SET @stat='Defaulter'
 End
else
Begin

if @stat='Refund'
 Set @stat='Refunded'
 if @stat='Cancled'
 Set @stat='Cancel'
 if @stat='Transfer'
 Set @stat='Transfered'
End

 UPDATE PaymentData
        SET stat =@stat



    WHERE customerID = @customerID

END

Answers


If I understand this properly, then the following should work... Give it a try and correct any syntax errors you find: NOTE: Personally, I would not use a trigger for this. I would put this code in the store procedure that applies the payment.

 ALTER TRIGGER [dbo].[OnpaymentUpdate]
   ON  [dbo].[paymentData]
 AFTER UPDATE --operations you want trigger to fire on
 As 

BEGIN   
    Declare @today datetime = dateAdd(dd, datediff(dd, 0, getDate()), 0)
    Declare @custInfo table 
        (custId integer Primary Key not null, 
         stat varChar(30) not null,
         balance bigint not null,
         lastPymnt datetime not null, 
         lastGap smallint not null)

    Insert @custInfo(custId, stat, balance, lastPymnt, lastGap)
    Select i.customerId, i.stat, 
        totalprice-(paidAmount+concession), 
        MAX(paymentDate),
        Min(datediff(month, paymentDate, @today))
    From inserted i 
       join paymentReceipt r On r.customerId = i.customerId
       join PaymentData d On d.CustomerId = i.customerId
    Group By i.customerId, i.stat, 
        d.totalprice-(d.paidAmount + d.concession)


    Update pd Set
       balanceAmount = i.balance,
       lastpaymentDate = i.lastPymnt,
       stat = case When lastGap >=2  and i.stat!='Cancel' 
                and i.stat!='Refund' And i.stat!='Refunded' 
                and i.stat!='Transfered' and i.stat!='Transfer') Then Case
                   When @month >= 2 And i.stat='Defaulter' Then 'Regular'
                   When @balance<=0 Then 'Payment Completed'
                   Else 'Defaulter' End
                Else Case @stat 
                   When 'Refund' Then 'Refunded'
                   When 'Cancled' Then 'Cancel'
                   When 'Transfer' Then 'Transfered' End
                End
    From PaymentData pd Join @custInfo i 
       On i.custId = pd.customerID
END

Need Your Help

avoid executing the same method in all scala actions in play framework

scala playframework

I have a trait which each controller inherits from. The requirement is to check the url in all the actions defined in the controller. I find it is pretty annoying to execute this method in each

How to use JUNG layout transformations correctly?

java jung

I have got some issues using the DAGLayout algorithm of JUNG and subsequently reading out the layout coordinates into my own data structure again.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.