SQL Server™ 2005 Bible

Chapter 24. Exploring Advanced T-SQL Solutions

In This Chapter

• A methodology for developing server-side–code databases

• Enforcing complex data validation

• Implementing complex custom referential integrity

• Enforcing custom row-level security

• Creating a data-audit trail

• Handling complex transactions

• Logically deleting and archiving old data

As the logical schema is implemented, many business rules and entity relationships are implemented as constraints. However, some of the business rules may be too complex to implement as constraints. These rules may be implemented either in the front-end application, in a middle tier, or in the database server. Of these three possible locations, the database server offers absolute compliance. There's no guarantee that future users will access the data solely through the current middle-tier object or front-end application.

Implementing business rules at the database-server level using T-SQL in triggers, stored procedures, and functions offers the same compelling benefits as constraints:

• The rules are absolute, and may not be ignored or bypassed by any DML (Data Manipulation Language—Select, Insert, Update, Delete) request.

• The rules are as close to the data as possible, improving data-access speed and reducing network round-trips.

Constraints, triggers, and stored procedures stand watch over the integrity of the data.

The past several chapters have discussed specific techniques for developing server-side code. A recurring theme in these chapters has been that processing ...