trigger to allow multiple updates

Question

I have this trigger I want to make it allow multiple row updates, currently it handling only single row update.when i update record, it says sub query returning more then 1 value..

   GO
    ALTER TRIGGER [dbo].[OnReceiptUpdate]
    ON  [dbo].[paymentReceipt]
    AFTER UPDATE 
    AS 
    BEGIN   
    SET NOCOUNT ON;
    Declare   @correctdate VARCHAR(19);
   Declare   @receiptNo VARCHAR(50);
DECLARE @customerID NCHAR(50)
SET @customerID= (SELECT customerID FROM inserted)
 set @correctdate =  (SELECT CONVERT(VARCHAR(19),paymentDate,103) FROM inserted)   
  set @receiptNo =  (SELECT receiptNo FROM inserted)      
   BEGIN

   UPDATE Paymentreceipt
       SET paymentDate = @correctdate 
    WHERE customerID = @customerID and receiptNo=@receiptNo  
   END
 END

how to handle thiss?? – Mirza Asghir Baig Jun 02 '13 at 12:17 — Mirza Asghir Baig, Jun 02 '13 at 12:17

score 2 · Accepted Answer · answered Jun 02 '13 at 12:46

2

Update p
Set p.paymentDate = CONVERT(VARCHAR(19),i.paymentDate,103)
From Paymentreceipt p
inner join inserted i
On p.customerID = i.customerID and p.receiptNo = i.receiptNo

should do it I think.

PS why is p.paymentdate a string? That's asking for it.

answered Jun 02 '13 at 12:46

Tony Hopkinson

20,172
3
31
39

score 1 · Answer 2 · answered Jun 02 '13 at 12:33

The easiest way is to use an update statement such as the one below in the Trigger.

UPDATE Paymentreceipt
SET paymentDate = CONVERT(VARCHAR(19),paymentDate,103)
FROM inserted
WHERE Inserted.receiptNo  = Paymentreceipt.receiptNo
AND Inserted.customerID  = Paymentreceipt.customerID

Note I don't have SQL server in front of me so the syntax might not be 100% correct but that gives you the general idea.

In general I try and avoid triggers but if your really need the trigger then use it but it may be possible to address this issue through the use of a stored procedure.

trigger to allow multiple updates

2 Answers2