SQL Server also supports features to enforce complex business rules, including referential integrity. Referential integrity is the process of preserving defined relationships between tables when you enter or delete records in those tables. Referential integrity maintains consistency between the primary and foreign keys in tables, as in "Don't delete customers with open orders." Complex business rules also involve relationships among multiple columns—often in multiple database tables—that must be enforced across all applications and users.
SQL Server provides full support for declarative referential integrity (DRI) as defined by the ANSI SQL-92 standard. DRI allows the database administrator (DBA) to define data integrity restrictions for a table as well as the relationship between tables, both of which are enforced by SQL Server automatically. DRI preserves the defined relationships between tables when records are inserted, updated, or deleted. DRI is based on FOREIGN KEY, PRIMARY KEY, or UNIQUE constraint relationships, and it automatically ensures that related key values are consistent.
Microsoft SQL Server supports WITH CHECK OPTION on views, which ensures that users query and modify only the data visible through the view. This option forces all data modification statements executed against the view to adhere to the criteria set within the SELECT statement defining the view. Combined with the appropriate security privileges, WITH CHECK OPTION provides a powerful way to ensure that specific users update only appropriate subsets of data.
Stored procedures are collections of SQL or C language statements that are precompiled and stored at the server. Developers can code complex queries and transactions into stored procedures and then invoke them directly from client applications or other servers over the network. In addition to offering performance benefits, stored procedures can be used to enforce organization-wide data integrity and business policies by ensuring that all updates occur in consistent and prescribed ways. Consider a funds transfer transaction: You can code the account credit and debit operations as well as the safety check logic. For example, you could make sure that the amount of money in the source account is greater then the amount to be transferred—by coding a single stored procedure called transfer_funds. The application would execute this stored procedure each time a funds transfer transaction occurs.
In combination with SQL Server advanced security, stored procedures offer particularly powerful ways to enforce data integrity in mixed application environments. For example, SQL Server can prevent users from doing ad hoc UPDATE, INSERT, and DELETE operations on tables, yet allow these operations to occur when they are part of a predefined stored procedure. This allows the system administrator to control exactly when and how data is changed for each user or group of users in the database.
SQL Server centrally enforces complex business rules with a type of stored procedure called a trigger. Whereas stored procedures must be called explicitly to be executed, triggers are automatically invoked, or triggered, by an attempt to insert, delete, or update a data field. By directly monitoring changes in the data, triggers implement "data-driven integrity" as opposed to "application-driven integrity."
Triggers prevent incorrect, unauthorized, or inconsistent changes to data. Triggers accomplish this by performing any number of actions whenever a specified change to a data object is attempted. Triggers can be nested and can cascade a change throughout related tables in a database, rollback transactions, issue messages to an online user, and post alerts to the Windows NT system-wide event log.
A key advantage of triggers is that they're automatic. They work no matter what caused the data modifications: a clerk's entry, a report calculation, or a front-end application's computations. Triggers are stored in the SQL Server data dictionary, eliminating the need for redundant integrity code in applications.