SQL Triggers and Account Balances

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Triggers and Account Balances

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-06 : 11:58:27

Sliver writes "We currently have a SQL Job that runs every 15 minutes. This job runs a stored procedure to sum up account balances.

UPDATE CUSTOMER
SET CUSTOMER.ACCTBAL = (SELECT CASE
WHEN SUM(TRANSACTIONS.TRANAMT) IS NULL THEN 0
ELSE SUM(TRANSACTIONS.TRANAMT)
END
FROM TRANSACTIONS
WHERE TRANSACTIONS.ACCTNUM = @ACCTNUM),
WHERE CUSTOMER.CUSTOMERID = @CUSTOMERID

Is there a better method to maintaining a balance field other than running a procedure to update it so often?
The Transactions table is about 1 million records and this job will not complete before the next one is scheduled to run.
Would a trigger here be more beneficial, or would it make it more difficult to maintain? How can I get rid of this job?

Thanks for your input!"

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-07-06 : 13:03:53

If you were to create a trigger for this and only fire on the given altered account it would reduce the time.

It would also make the Balance Real-time.

However my preference would be to incorporate it into the SP that alters, inserts or deletes the transaction for that given customer. This would be the most efficient.

Jim
Users <> Logic

Also I would Change TO

CUSTOMER.ACCTBAL = (SELECT CASE
WHEN SUM(ISnull(TRANSACTIONS.TRANAMT, 0))IS NULL THEN 0
ELSE SUM(ISnull(TRANSACTIONS.TRANAMT, 0))
END

This way if there is a transaction with null data it will not zero out all other transactions.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-06 : 16:23:17

Why don't you just create a view?

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-07-06 : 16:30:06

Most of the accounting packages I have seen do have a running account balance field in the customer table.

I think it is a hold-over from the old days.

Just like batching invoices.

Jim
Users <> Logic

Just Like me LOL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources