| Author |
Topic |
|
mary_itohan
Posting Yak Master
191 Posts |
 Posted - 2008-07-25 : 19:14:40
|
| Hello All,I was adviced to avoid using triggers as they require a lot of memory and disk usage.I am developing a payment gateway with table that has user credits/money and a billing system.I have a table with the users credits called money and another table called transactions that logs all the users spend, then updates the money table.Is it proper to use a trigger here ? As we have a lot of activity or do i use a sp ?thanksMYes O ! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-25 : 19:44:56
|
| There is nothing wrong with using triggers properly.What is updated in the money table when a transaction occurs? Is a record added to the table, recording the appropriate debit? Or is an existing record for the user updated to reflect the new balance.e4 d5 xd5 Nf6 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-26 : 11:25:38
|
quote:
Originally posted by mary_itohan
Hello All,I was adviced to avoid using triggers as they require a lot of memory and disk usage.I am developing a payment gateway with table that has user credits/money and a billing system.I have a table with the users credits called money and another table called transactions that logs all the users spend, then updates the money table.Is it proper to use a trigger here ? As we have a lot of activity or do i use a sp ?thanksMYes O !
Can you explain your full requirement so that we can see if there's any other approach that can be used. |
|
|
|
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-07-26 : 13:45:26
|
| Its a billing system that checks the following.1. table credits2. table accounting/historywhen user purchases something, the accounting table is updated with a SP. Which then fires a trigger that checks to make sure the values submited are ok before updating the credits table.we just want to avoid negative billing and make sure accounting is properly done before updateshope this is clear.Yes O ! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-26 : 14:02:35
|
quote:
Originally posted by mary_itohan
Its a billing system that checks the following.1. table credits2. table accounting/historywhen user purchases something, the accounting table is updated with a SP. Which then fires a trigger that checks to make sure the values submited are ok before updating the credits table.we just want to avoid negative billing and make sure accounting is properly done before updateshope this is clear.Yes O !
Yup. this can be done with a trigger. And if you're using SQL 2005 you can even use OUTPUT clause to do this inside SP itself. Also do what action do you want to perfom if negative billing happens/ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-26 : 22:28:44
|
| If you are simply using the trigger to update the data, then yes you can do it this way.If you are using the trigger to check another table to see if the update to the triggering table is allowed, that gets a little hazy. It would be ok to use it for the validation, but you should really check the status of the account in the sproc before the first table is ever updated.e4 d5 xd5 Nf6 |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-07-27 : 08:49:10
|
| Personally I think triggers make life difficult by hiding behaviour and introducing side effects. In this case it is purely an application thing so I would just write my SP to work accordingly without hiding the logic in a bizarre trigger type thing.You might also want to consider constraints to ensure numeric values are positive for example. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-27 : 11:21:24
|
| Personally, I think triggers make life easier by hiding behavior.That is, after all, the whole point of Object Oriented programming.And they only introduce side-effects if they were poorly written, the same as any other code.Keep your data logic as close to the data as possible. That means using, in order of their distance from the data:1) Datatypes2) Constraints and indexes3) Triggers4) Procedures and views5) Application codee4 d5 xd5 Nf6 |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-07-30 : 04:15:33
|
| As a general rule, I would swap 3 & 4.While I get what you are saying, I don't find the "unexpected" behaviour of triggers to be as intuitive as when you get a different behaviour using OO method overrides/inheritance.Granted, the SP route can limit how much you can do with sets. Horses for courses... |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-30 : 09:54:15
|
| If you swap 3 & 4, you may find yourself having to duplicate code logic in several stored procedures that access that table. IF the logic changes, you (or the DBA who manages the system three years from now) will have to make sure they find every place that rule is implemented.e4 d5 xd5 Nf6 |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-07-30 : 21:49:31
|
| Well now you're simply talking about well understood problems such as having code/rules in one place etc. I know what I am doing and I'm sure you do as well but I don't think either of us or anyone reading this is going to read the other's post and have an epiphany or anything, so I'm not going to add any more. |
|
|
|
|
When do i use triggers