0

I have 3 tables and a trigger in the Purchase table.

User (UserId | Balance)

Purchase (PurchaseId | UserId | Amount | IsConfirm)

TransactionDetail (TrxId | UserId | Balance |Amount)

When the Purchase IsConfirm is updated from False to True, I want the trigger to update Player Balance and insert new record(s) into TransactionDetail.

I have this bulk update statement:

UPDATE Purchase SET IsConfirm = true WHERE UserId=1

I know that trigger will only be fired once for a batch update. So if there are 5 rows updated, there is only 1 row inserted into the TransactionDetail table, and the balance is not updated correctly.

If there are 5 rows affected in a batch update,
by using trigger, is there any way to insert 5 updated rows into the TransactionDetail and update the user balance correctly?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
EpoWilliam
  • 113
  • 1
  • 9
  • sure, check the inserted and deleted pseudo tables inside the trigger. or, even better, stay away from the triggers.. – dean Mar 17 '15 at 08:13
  • Generally, you shouldn't store *calculated* data (such as the user's balance, here) in a database, unless there's a specific performance reason to do so (and often, even then, you can get SQL Server to do the hard work of *maintaining* that data). It's just an invitation for the calculated data to get out of sync with reality. If you always just access the `SUM(Balance)` (or `Amount`, bit confusing having both in the transaction table) then you'll always get the correct value. – Damien_The_Unbeliever Mar 17 '15 at 09:36

0 Answers0