Basic business rules question

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

Bad results from my MDX Adventure Works query

I have an Adventure Works MDX query that I want to return all the male employees and their total reseller-sales (including the people below them)

Select [Measures].[Reseller Sales-Sales Amount] on Columns,
non empty
Exists(
[Employee].[Employees].AllMembers
,[Employee].[Gender].&[M]
) on Rows
from [Analysis Services Tutorial]

which, when run, returns

Reseller Sales-Sales Amount
All Employees $80,450,596.98
Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Amy E. Alberts $15,535,946.26
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38
Syed E. Abbas $172,524.45

so there are three problems with this result, and I think they all have 1 solution. First, I don't want the 'All Employees' row. Second, there is a female in my results, seemingly because this female is the supervisor of some of the males. I asked for no females in my query. Third, Syed shows up twice, because he is a supervisor and a salesman himself. What I want are these results..

Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38

Notice that there are no "All Employees", the woman is gone, and Syed is only a supervisor, and not an underling also. What MDX query would give me these results?

Thanks,

Todd Wilder

Referring to my response to your earlier post, this query seems to return the results you want - except for the order:

Select [Measures].[Reseller Sales Amount] on Columns,
non empty Generate(exists([Employee].[Employee].[Employee],
[Employee].[Gender].&[M]),
{LinkMember([Employee].[Employee].CurrentMember,
[Employee].[Employees])}) on Rows
from [Adventure Works]

|||Your queries dont seem to return anything on my cubes - your measure is named slightly different then mine and I don't have any tuples like [Employee].[Employee].[Employee]. Where did your cube come from?|||

Are you aware of the Adventure Works standard sample cube?

http://msdn2.microsoft.com/en-us/library/ms143804.aspx

>>

SQL Server 2005 Books Online

Running Setup to Install AdventureWorks Sample Databases and Samples

Updated: 17 July 2006

The AdventureWorks (OLTP), AdventureWorksDW (data warehouse), and Adventure Works DW (analysis services) sample databases, as well as the companion samples, are not installed by default in SQL Server 2005. You can download these from SQL Server 2005 Samples and Sample Databases at the Microsoft Download Center, or you can use the following procedures to install the sample databases and samples during or after setup. Additional instructions for deploying the Adventure Works DW analysis services project are also provided.

...

>>