Page 83 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

We have added the udf_GetProductsByCategoryID UDF to the Northwind database; Figure Notes
below displays the output of the above SELECT query when observed via Management Studio.
UDFs that return tabular data can be located in the Object Explorer s Table-value Functions
folder.

5.10.3 Triggers

Microsoft® SQL Server™ 2000 provides two primary mechanisms for enforcing business rules
and data integrity: constraints and triggers. A trigger is a special type of stored procedure that
automatically takes effect when the data in a specified table is modified. A trigger is invoked in
response to an INSERT, UPDATE, or DELETE statement. A trigger can query other tables and can
include complex Transact-SQL statements. The trigger and the statement that fires it are treated
as a single transaction, which can be rolled back from within the trigger. If a severe error is
detected (for example, insufficient disk space), the entire transaction automatically rolls back.

Triggers are useful in these ways:
Triggers can cascade changes through related tables in the database; however, these changes
can be executed more efficiently using cascading referential integrity constraints.

Triggers can enforce restrictions that are more complex than those defined with CHECK
constraints.
Unlike CHECK constraints, triggers can reference columns in other tables. For example, a
trigger can use a SELECT from another table to compare to the inserted or updated data and to
perform additional actions, such as modify the data or display a user-defined error message.
Triggers can also evaluate the state of a table before and after a data modification and take
action(s) based on that difference.

LOVELY PROFESSIONAL UNIVERSITY 77