After delete Trigger on Transaction
-
I have 3 table
1. JournalParent
1. counter int (used for relation to journalDetail)
2. date datetime
2. journalDetail
1. counter int (used for relation to journalparent)
2. account nchar
3. debit money
4. credit money
3. account
1. account nchar
2. balance money
then, i create form to maintain journal transaction (insert & delete), at that form i used transaction mode so if both
journalDetail & journalParent will be dill properly
and then i create 2 trigger to add or minus balance on account table
1. *** Trigger after inserted to add account balance
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [AddAccountBalance] ON [dbo].[journalChild] AFTER INSERT AS
BEGIN
UPDATE accountBalance
SET balance = balance + (SELECT debit-credit FROM INSERTED)
WHERE code = (SELECT account FROM INSERTED)
END
*** this trigger working well (update balance on account table properly)
2. *** Trigger after delete to minus account balance
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [AddAccountBalance] ON [dbo].[journalChild] AFTER DELETE AS
BEGIN
UPDATE accountBalance
SET balance = balance - (SELECT debit-credit FROM DELETED)
WHERE code = (SELECT account FROM DELETED)
END
*** this trigger not working well (error message SUBQUERY RETURED MORE THAN 1 VALUE)
I add 1 transaction like below:
counter : 1
date : 24 december 2005
account debit credit
001 1,000
002 1,000
When i add this transaction after inserted trigger run properly (so account balance for 001=1,000 and 002= -1000)
but when i delete this transaction, come out error message "SUBQUERY RETURED MORE THAN 1 VALUE"
***(below delete command from form)
try
{
myAtomCmd.CommandText = string.Format("DELETE FROM JournalParent WHERE counter = '{0}'", myRecordKey);
myAtomCmd.ExecuteNonQuery();
myAtomCmd.CommandText = string.Format("DELETE FROM JournalChild WHERE counter = '{0}'", myRecordKey);
myAtomCmd.ExecuteNonQuery();
myAtom.Commit();
}
...so on
So i open journalDetail table and delete record one by one the after delete trigger run properly
Please advise from you all... thank
regards
Sandi Antono
-
Change
UPDATE accountBalance
SET balance = balance - (SELECT debit-credit FROM DELETED)
WHERE code = (SELECT account FROM DELETED)
to
UPDATE accountBalance
SET accountBalance.balance = accountBalance.balance - del.debit + del.credit
FROM deleted del, accountBalance
WHERE accountBalance.code = del.Account
N 56°04'39.16"
E 12°55'05.25" -
Your problem occurs when you have multiple deletes invlolving multiple accounts
This would be my (ANSI-92 standard) way of doing it
UPDATE a
SET a.balance = a.balance - d.adjustment
FROM accountBalance a
INNER JOIN (SELECT account,SUM(debit-credit) AS [adjustment] FROM DELETED GROUP BY account) d
ON a.code = d.account
Far away is close at hand in the images of elsewhere.
Anon. -
Thank's to you all, especially to Peter Larsson (journeyMan) and DavidsBurrows (SSC Ommited), it's work and base on that i make my own style too, which make me understood more easier
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply