June 12, 2006 at 6:00 am
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
June 15, 2006 at 8:00 am
This was removed by the editor as SPAM
June 16, 2006 at 7:34 am
You need to change both triggers as follows (in red).
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 ISNULL(SUM(debit),0)-ISNULL(SUM(credit),0) 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 ISNULL(SUM(debit),0)-ISNULL(SUM(credit),0) FROM DELETED)
WHERE code = (SELECT account FROM DELETED)
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply