would this be too much processing ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

would this be too much processing ?

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-06-22 : 13:05:10

hi,
i have a billing system for web2phone sms.

However, i was thinking of an added security feature to add a trigger to the credits table to scan through the history table to see if the user actually has his debit/credits and accounts up to date whenever an update is made to his account

Simply for security and accounting purposes.

Now, we have a large client base, querying this table and a good number (and still increasing) numbers in the history table.

Would this be efficient or too much work ?

Afrika

Kristen
Test

22859 Posts

Posted - 2005-06-22 : 14:43:59

If I've understood your problem correctly:

I would use a TRIGGER to maintain an "account balance" so that I don't have to SUM a bunch of transaction each time I need to know what the Balance is - also allows stale transactions to be deleted and the Balance is preserved

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-06-23 : 03:54:46

Yes, Kristen.

The only little issue is that everytime an update is made. The trigger would scan through the changes to maintain the account balance.

But pls note: Updates are made regularly to the system.

would this be too much processing ?

Kristen
Test

22859 Posts

Posted - 2005-06-23 : 09:17:09

Ah, I wouldn't do it like that!

I'd use the "before" and "after" to work out by how much that single transaction had changed, and adjust the Balance by that amount.


CREATE TRIGGER MyTrigger
ON MyTransactionTable
AFTER INSERT, UPDATE, DELETE
AS
UPDATE A
SET    MyBalance = COALESCE(A.MyBalance, 0)
                 + COALESCE(I.MyAmount, 0)
                 - COALESCE(D.MyAmount, 0)
FROM   MyTransactionTable T
       JOIN MyAccountTable A
           ON A.MyAccountCode = T.MyAccountCode
       LEFT OUTER JOIN inserted I
           ON I.MyPK = T.MyPK
       LEFT OUTER JOIN deleted I
           ON D.MyPK = T.MyPK

EDIT: There might be an issue if there were two transactions for the same account in the same update batch - you'd need some sort of GROUP BY to take care of that
Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-06-23 : 12:47:29

Sounds ok, would try that and let you know

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources