I have a requirement similar to previous discussions at:
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
Trigger in combination with transaction
I have two tables, [Account].[Balance] and [Transaction].[Amount]:
CREATE TABLE Account (
...
#2 Why do you want to do it in SQL and not in an application? Also have you considered integrity checking as a scheduled task? (Go through each account and ensure each balance matches the transactions)
@Paul a scheduled task is not fast enough. Imagine my account balance is $100, I can take out $100, then if I try again before the scheduled task runs, I could take out $100 again.
@Paul From the question, it seems the only problem trying to be solved is balance modifications..
@Yiping I get that but I don't know how you will come to a greater understanding about triggers if you use an unsafe, asynchronous scheduled task to consolidate account balances.
@AaronBertrand there's no need to be snippy. I've seen you go off on tangents that don't speak directly to a question being asked. And as stated, no one said to use tasks to consolidate the balances.
@Paul how exactly am I being snippy? I'm not going off on any tangents. The OP is asking about how to consolidate account balances when transactions take place, and you recommended using a scheduled task after the fact. I think for a bank it's very important to understand the problem with that recommendation.
@Paul what exactly were you suggesting to use tasks for, then, since the only code in the trigger in the question is consolidating account balances?
@AaronBertrand I DID NOT recommend that. Forgive the caps but it's the 3rd time I've needed to state it to you. I quite clearly stated a consideration for a checking process outside of the consolidation process. The reason for this is to ensure the balance hasnt become invalid in some way. e.g. altered outside of the trigger, transaction values changed outside of the business layer, business rules being changed without thought to prior timeline. I worked with such a system for 10 years and integrity of the account values was always being put at risk by sloppy code and bespoke account 'fixes'
@Paul go ahead. I read "Also have you considered ..." and "Have you considered also ..." quite differently. I'm not sure where you learned English, but I don't think it's as clear as you think that you meant that as an additional check rather than a different approach. After all, your first sentence also sounded like you wanted the OP to do this in the app instead of in the trigger.
Hi, would you like me to point out again why I found your statement misleading, and why the placement of the word "also" is very important?
I guess not. I have better things to do with my day than arguing about your mis-stated recommendation. Ciao.
@AaronBertrand from dictionary.com for 'also' "1. in addition; too; besides; as well: He was thin, and he was also tall. ". Seems clear to me from just that. Never mind I posited using a task to "integrity check", to check balances match transactions. Not to set balances to transactions. Asking me where I learned English (England btw) that's an example of being snippy.
@Paul: "also" means "in addition", true. But it was used in a new sentence, so I wouldn't understand it was meant that way. It seems to introduce another alternative.
Earlier today on another site in the network, a user posted a question like this:
Is there a better way to use [x]?
There was a lot more to it, but the very first comment addressed this question directly and explicitly:
Why do you want to use [x] and not [y]? Also have you considered us...
@AaronBertrand I like the way you framed that query. But I believe you misrepresented the situation. If the initial query is "Is there a better way to use [x] to achieve [1]?" then the follow up comment from me was "Why do you want to use [x] and not [y] to achieve[1]? Also have you considered using [z] to achieve [2]?". I think trying to add that to your existing question without muddling up those already involved would be quite futile. Anyway, I'm about to log off for the day, have a good one.
Paul, your comment was actually: Why do you want to do it in SQL and not in an application? Also have you considered integrity checking as a scheduled task?
which is basically Why do you want to do [x] and not [y]? Also have you considered [z]
@Paul what's at issue is the interpretation of your comment, and how it doesn't mean what you think it means. What led to the comment is largely irrelevant.
I said "Also have you considered integrity checking as a scheduled task?" which I believe implies a new idea (the integrity checking, or [2] if we prefer). If I had said "Also have you considered doing this as a scheduled task?" then it would tie to your point.
Maybe we should just leave it at the things posted then. In the future I will endeavour to be (even more) clear and hopefully Aaron will try to accept on the first 1-2 tries that when someone says they didn't say something, they didn't attempt to communicate what he believes.
As a moderator on the site, it is important for me to interpret things the way they are written, and the way most other readers will interpret them. Saying that's not what you meant in a subsequent comment is not very useful for someone who only reads your initial, misleading comment. Sorry.
@AaronBertrand I disagree it was misleading. You've also misrepresented the English SE question. I think this is going nowhere though, hopefully we all take away something from it.
Earlier today on another site in the network, a user posted a question like this:
Is there a better way to use [x]?
There was a lot more to it, but the very first comment addressed this question directly and explicitly:
Why do you want to use [x] and not [y]? Also have you considered us...
Discussion between Paul and Aaron Ber…
Discussion between Paul and Aaron Ber…