After delete Trigger on Transaction
- From: sandi@xxxxxxxxxxxx
- Date: 12 Jun 2006 05:04:27 -0700
Please Advise ..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
.
- Prev by Date: Re: Web Service and Client
- Next by Date: Re: Strongly typed dataset and transactions
- Previous by thread: Web Service and Client
- Next by thread: Re: Date time format trouble
- Index(es):
Relevant Pages
|
