How do you represent business rules in SQL Server? I need the database to return an error to a .net app if a business rule fails.
Thank you! :)
I'm not sure if this is what you mean, but you can use constraints (like CHECK CONSTRAINTS and foreign key constraints) and triggers to enforce business rules.|||Thanks. Yes, I mean something that allows me to create an elaborate procedure to verify compliance with a business rule. Are triggers the only way? I've heard negative things about triggers.|||If you can use constraints then you should because they're more optimal in performance (I think) - and you can use user-defined functions in the constraints to have fairly complex logic. But if not (because the rules are elaborate) I don't see what's wrong with triggers - they are a standard way of enforcing business rules. What negative things did you hear?|||Thanks, mostafae. I can't remember specifically what I heard; I just remember hearing that it's best to avoid them for some reason. But I don't see what the problem is, either. Thanks, again.|||triggers can cause problems and many people (Self included) recommend against them
1. if the trigger contains much logic, it can hurt performance
2. if you're trouble shooting a slow stored procedure, you will NOT see the trigger code in any query plan. in fact, you wont even know the trigger exists unless you explicitly go look for it. This can cause you to spin your wheels trouble shooting the sproc, when the performance issue is in the trigger all along. (Maintenance nightmare)
3. it's preferable to use constraints
4. IF you must use triggers, be sure to kee them SIMPLE and FAST.
Cheers
|||1. ... on the other hand, much logic might legitimately be needed for a particularly complex business rule. Is there another way to represent such a rule?
2. How disappointing.
3. Can constraints handle complex business rules that require some process to execute?
4. In what situations might you have no choice but to use Triggers?
:)
|||well.....
therein lies the question and the topic of debate right...?
You may be forced to use triggers IF for some reason users are accessing your DB without going through a Business Tier.
If users can access the system and circumvent the Application, you could be hosed. If you want to be 100% certain that business logic ALWAYS Fires, regardless of how the DB is accessed, then you may be forced to use triggers (you poor soul).
However, if you're in an environment that allows users to access the DB directly, you have more problems than fiddling with triggers anyway.
The performance problem is not really triggers, it's "business logic" on the Database server. it would be the same thing with containing tons of logic in a sproc. You dont want to have a ton of business logic in a sproc (in my opinion) you want your sprocs to be "Primitive" in nature (Simple CRUD ops). The reason for this is not JUST performance, it's also scalability.
if you take complex logic in a sproc and emulate the same progic in compiled code (C# for example), it's going to perform better due to the fact that it's compiled. Furthermore, it's MUCH easier to scale application servers than DB Servers. In a SQL Cluster you can never have more then 3 boxes, so if your SQL Servers are doing a lot of Business processing you "could" hit the wall quite quickly (think LARGE scale applications like on-line banking, etc). IF you move most or all business logic to business tier (Not just logically, but physically to app servers), you have to ability so scale out as much as you need. You can have as many applications servers as you can afford to purchase.
so, Triggers are not necessarily evil, they are just a trade off in architectural decision making....
I hope this makes sense. Constraints are only going to allow you to implement "Simple rule Logic"
Cheers
|||Thanks, pdxJaxon. That addresses my concerns, very well, actually.
sql