How to write the trigger that works when I am deleting the data?

Question

Hai,

I have the following table,

Table1: tblTransaction

| SlNo | Date | ClientID | productID | Amount | Weight |

Table2: tblAccount

| ClientID | Name | Description | BalanceAmount | BalanceWeight |


the problem is when I issue a transaction it may have more than one product so in the same SlNo there are more than one rows in the table

The trigger I write like this,

-------------------------------------------------------------------

CREATE TRIGGER myDeleteTrigger on tblTransaction
AFTER Delete
AS
BEGIN
 Declare @SlNo as nvarchar(20),
 Declare @ClientID as nvarchar(20),
 Declare @Amount as decimal(18,2),
 Declare @Weight as decimal(18,3)

 set @SlNo = select SlNo from deleted
 set @Amount = select Amount from Deleted
 set @Weight = select Weight from deleted
 set @ClientID = select ClientID from deleted
 set @BalanceAmount = select BalanceAmount from tblAccount where ClientID = @ClientID
 set @BalanceWeight = select BalanceWeight from tblAccount where ClientID = @ClientID

 Update tblAccount set BalanceAmount = @BalanceAmount - @Amount where ClientID = @ClientID
 Update tblAccount set BalanceWeight = @BalanceWeight - @Weight where ClientID = @ClientID

--------------------------------------------------------------------

here the problem is since I have more than rows with the same SlNo, error occurs during this trigger works
so I think my problem is solved if I get the code to take sum of Weight Deleted

If any body know more about trigger and any other solution to solve my problem please help me

Thanks in Advance

Answer 1

http://www.c-sharpcorner.com/UploadFile/mahesh/CreatingDBProgrammaticallyMCB11282005064852AM/CreatingDBProgrammaticallyMCB.aspx