Nielsen c25.tex V4 - 07/23/2009 4:55pm Page 632
Part IV Developing with SQL Server
Anderson 01-016 2001-11-16 00:00:00.000 Outer Banks Lighthouses
Andrews 01-015 2001-11-05 00:00:00.000 Amazon Trek
Andrews 01-012 2001-09-14 00:00:00.000 Gauley River Rafting
Andrews 01-014 2001-10-03 00:00:00.000 Outer Banks Lighthouses
Bettys 01-013 2001-09-15 00:00:00.000 Gauley River Rafting
Bettys 01-015 2001-11-05 00:00:00.000 Amazon Trek

The second form, the OUTER APPLY command, operates much like a left outer join. With this usage,
rows from the main query are included in the result set regardless of whether the virtual table returned
by the user-defined function is empty.
Creating functions with schema binding
All three types of user-defined functions may be created with the significant added benefit of schema
binding. Views may b e schema bound; in this way, UDFs are like views — both can be schema bound.
This is one reason why you might choose a UDF over a stored procedure, as stored procedures cannot
be schema bound. Schema binding prevents the altering or dropping of any object on which the func-
tion depends. If a schema-bound function references
TableA, then columns may be added to TableA,
but no existing columns can be altered or dropped, and neither can the table itself.
To create a function with schema binding, add the option after
RETURNS and before AS during function
creation, as shown here:
CREATE FUNCTION FunctionName (Input Parameters)
RETURNS DataType
WITH SCHEMA BINDING
AS
BEGIN;
Code;
RETURNS Expression;
END;

Schema binding not only alerts the developer that the change will may affect an object, it prevents the
change. To remove schema binding so that changes can be made,
ALTER the function so that schema
binding is no longer included.
Multi-Statement Table-Valued Functions
The multi-statement table-valued user-defined function combines the scalar function’s ability to contain
complex code with the inline table-valued function’s ability to return a result set. This type of function
creates a table variable and then populates it within code. The table is then passed back from the func-
tion so that it may be used within
SELECT statements.
The primary benefit of the multi-statement table-valued user-defined function is that complex result sets
may be generated within code and then easily used with a
SELECT statement. This enables you to build
complex logic into a query and solve problems that would otherwise be very difficult to solve without
acursor.
632
www.getcoolebook.com
Nielsen c25.tex V4 - 07/23/2009 4:55pm Page 633
Building User-Defined Functions 25
The APPLY command may be used with multi-statement table-valued user-defined functions in the same
way that it’s used with inline user-defined functions.
Creating a multi-statement table-valued function
The syntax to create the multi-statement table-valued function is very similar to that of the scalar user-
defined function:
CREATE FUNCTION FunctionName (InputParamenters)
RETURNS @TableName TABLE (Columns)
AS
BEGIN;
Code to populate table variable
RETURN;

END;
The following process builds a multi-statement table-valued user-defined function that returns a basic
result set:
1. The function first creates a table variable called
@Price within the CREATE FUNCTION
header.
2. Within the body of the function, two
INSERT statements populate the @Price table variable.
3. When the function completes execution, the
@Price table variable is passed back as the
output of the function.
The
ftPriceAvg function returns every price in the Price table and the average price for each
product:
USE OBXKite;
go
CREATE FUNCTION ftPriceAvg()
RETURNS @Price TABLE
(Code CHAR(10),
EffectiveDate DATETIME,
Price MONEY)
AS
BEGIN;
INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, EffectiveDate, Price
FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID;
INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, Null, Avg(Price)

FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID
633
www.getcoolebook.com
Nielsen c25.tex V4 - 07/23/2009 4:55pm Page 634
Part IV Developing with SQL Server
GROUP BY Code;
RETURN;
END;
Calling the function
To execute the function, refer to it within the FROM portion of a SELECT statement. The following code
retrieves the result from the
ftPriceAvg function:
SELECT *
FROM dbo.ftPriceAvg();
Result:
Code EffectiveDate Price

1001 2001-05-01 00:00:00.000 14.9500
1001 2002-06-01 00:00:00.000 15.9500
1001 2002-07-20 00:00:00.000 17.9500
Multi-statement table-valued user-defined functions use tempdb to pass the table variable
to the calling query. For many applications this is not a concern, but for high-transaction
applications I recommend focusing on the performance of the UDF and, if possible, incorporating the
code directly into the calling stored procedure.
Summary
User-defined functions expand the capabilities of SQL Server objects and open a world of flexibility
within expressions and the
SELECT statement.

The big ideas from this chapter:
■ Scalar user-defined functions return a single value and must be deterministic.
■ Inline table-valued user-defined functions are very similar to views, and return the results of a
single
SELECT statement.
■ Multi-statement table-valued user-defined functions use code to populate a table variable,
which is then returned.
■ The
APPLY function can be used to pass data to an inline table-valued UDF or a multi-
statement table-valued UDF from the outer query, similar to how a correlated subquery can
receive data from the outer query.
T-SQL code can be packaged in stored procedures, user-defined functions, and triggers. The next
chapter delves into triggers, specialized T-SQL procedures that fire in response to table-level events.
634
www.getcoolebook.com
Nielsen c26.tex V4 - 07/23/2009 4:56pm Page 635
Creating DML Triggers
IN THIS CHAPTER
Creating instead of and
after triggers
Using the transaction’s data
within the trigger
Integrating multiple triggers
Building a delete trigger to
enforce a single-row delete
T
riggers are special stored procedures attached to table events. They can’t
be directly executed; they fire only in response to an
INSERT, UPDATE,
or

DELETE event on a table. In the same way that attaching code to a
form or control event in Visual Basic or Access causes that code to execute on
the form or control event, triggers fire on table events. Users can’t bypass a trig-
ger; and unless the trigger sends a message to the client, the end-user is unaware
of the trigger.
Developing triggers involves several SQL Server topics. Understanding transaction
flow and locking, T-SQL, and stored procedures is a prerequisite for developing
smooth triggers. Triggers contain a few unique elements and require careful plan-
ning, but they provide rock-solid execution of complex business rules and data
validation.
Trigger Basics
SQL Server triggers fire once per data-modification operation, not once per
affected row. This is different from Oracle, which can fire a trigger once
per operation, or once per row. While this may seem at first glance to be a
limitation, being forced to develop set-based triggers actually helps ensure clean
logic and fast performance.
Triggers may be created for the three table events that correspond to the three
data-modification commands:
INSERT, UPDATE,andDELETE.
635
www.getcoolebook.com
Nielsen c26.tex V4 - 07/23/2009 4:56pm Page 636
Part IV Developing with SQL Server
Best Practice
T
riggers extend the duration of a transaction, which can lead to locking and blocking problems for
high-transaction systems. For data integrity, sometimes a trigger is the best solution, but be aware of the
potential performance impact. If the processing can be performed in the abstraction layer with 100 percent
certainty, then I’d rather see the code there than in a trigger. If the abstraction layer isn’t enforced
100 percent of the time, then the code must exist in a trigger.

SQL Server has two kinds of transaction triggers: instead of triggers and after triggers. They differ in their
purpose, timing, and effect, as detailed in Table 26-1.
Database triggers fire on data definition language (DDL) commands — CREATE, ALTER,
DROP
— and are useful for auditing server or database schema changes. For more details,
see Chapter 27, ‘‘Creating DDL Triggers.’’
TABLE 26-1
Trigger Type Comparison
Instead of Trigger After Trigger
DML statement Simulated but not
executed
Executed, but can be rolled back in the
trigger
Timing Before PK and FK
constraints
After the transaction is complete, but
before it is committed
Number possible per table
event
One Multiple
May be applied to views? Yes No
Nested? Depends on server
option
Depends on server option
Recursive? No Depends on database option
Transaction flow
Developing triggers requires understanding the overall flow of the transaction; otherwise, conflicts
between constraints and triggers can cause designing and debugging nightmares.
Every transaction moves through the various checks and code in the following order:
1.

IDENTITY INSERT check
2. Nullability constraint
636
www.getcoolebook.com
Nielsen c26.tex V4 - 07/23/2009 4:56pm Page 637
Creating DML Triggers 26
3. Data-type check
4.
INSTEAD OF trigger execution. If an INSTEAD OF trigger exists, then execution of the DML
stops here.
INSTEAD OF triggers are not recursive. Therefore, if the INSERT trigger executes
another DML command, then the
INSTEAD OF trigger will be ignored the second time around
(recursive triggers are covered later in this chapter).
5. Primary-key constraint
6. Check constraints
7. Foreign-key constraint
8. DML execution and update to the transaction log
9.
AFTER trigger execution
10. Commit transaction (for more details on commits, see Chapter 66, ‘‘Managing Transactions,
Locking, and Blocking’’)
Based on SQL Server’s transaction flow, note a few key points about developing triggers:
■ An
AFTER trigger occurs after all constraints. Because of this, it can’t correct data, so the data
must pass any constraint checks, including foreign-key constraint checks.
■ An
INSTEAD OF trigger can circumvent foreign-key problems, but not nullability, data-type, or
identity-column problems.
■ An

AFTER trigger can assume that the data has passed all the other built-in data-integrity
checks.
■ The
AFTER trigger occurs before the DML transaction is committed, so it can roll back the
transaction if the data is unacceptable.
Creating triggers
Triggers are created and modified with the standard DDL commands, CREATE, ALTER,andDROP,as
follows:
CREATE TRIGGER Schema.TriggerName ON Schema.TableName
AFTER | INSTEAD OF [Insert, Update, (and or) Delete]
AS
Trigger Code;
The trigger can be fired for any combination of insert, update, or delete events.
Prior to SQL Server 2000, SQL Server had
AFTER triggers only. Because no distinction between AFTER
and INSTEAD OF was necessary, the old syntax created the trigger FOR INSERT, UPDATE,orDELETE.
To ensure that the old
FOR triggers will still work, AFTER triggers can be created by using the keyword
FOR in place of AFTER.
Although I strongly recommend that triggers be created and altered using scripts and version
control, you can view and modify triggers using Management Studio’s Object Explorer, as shown in
Figure 26-1.
637
www.getcoolebook.com
Nielsen c26.tex V4 - 07/23/2009 4:56pm Page 638
Part IV Developing with SQL Server
FIGURE 26-1
Object Explorer will list all triggers for any table and may be used to modify the trigger using the
context menu.
After triggers

A table may have several AFTER triggers for each of the three table events. AFTER triggers may be
applied to tables only, not to views.
The traditional trigger is an
AFTER trigger that fires after the modification implied by the statement is
complete, but before the statement ends and before the transaction is committed.
AFTER triggers are
useful for the following:
■ Complex data validation
■ Enforcing complex business rules
■ Writing data-audit trails
■ Maintaining modified date columns
■ Enforcing custom referential-integrity checks and cascading deletes
638
www.getcoolebook.com
Nielsen c26.tex V4 - 07/23/2009 4:56pm Page 639
Creating DML Triggers 26
Best Practice
W
hen planning triggers, consider the most likely path. If the trigger verifies data that will nearly always
be accepted, then an AFTER trigger is the best route. That’s because the work is completed and the
trigger is merely a check.
For inserts, updates, or deletes that are rarely accepted, use an INSTEAD OF trigger, which doesn’t actually
perform the DML statement’s work prior to the trigger’s execution.
When you are learning a new programming language, the first program you write is traditionally a ‘‘hello
world’’ application that does nothing more than compile the program and prove that it runs by printing
‘‘hello world.’’ The following
AFTER trigger simply prints ‘In the After Trigger’when the trigger
is executed:
USE Family;
CREATE TRIGGER dbo.TriggerOne ON dbo.Person

AFTER INSERT
AS
PRINT ‘In the After Trigger’;
With the AFTER trigger enforced, the following code inserts a sample row:
INSERT dbo.Person(PersonID, LastName, FirstName, Gender)
VALUES (50, ‘Ebob’, ‘Bill’,‘M’);
Result:
In the After Trigger
(1 row(s) affected)
The INSERT worked and the trigger printed its own version of the ‘‘hello world’’ message.
Instead of triggers
INSTEAD OF triggers execute ‘‘instead of’’ (as a substitute for) the submitted transaction, so that the sub-
mitted transaction does not occur. It’s as if the presence of an
INSTEAD OF trigger signals the submitted
transaction to be ignored by SQL Server.
As a substitution procedure, each table is limited to only one
INSTEAD OF trigger per table event. In
addition,
INSTEAD OF triggers may be applied to views as well as tables.
Don’t confuse
INSTEAD OF triggers with BEFORE triggers or before update events. They’re not the
same. A
BEFORE trigger, if such a thing existed in SQL Server, would not interfere with the submitted
DML statement execution unless code in the trigger executed a transaction rollback.
639
www.getcoolebook.com
Nielsen c26.tex V4 - 07/23/2009 4:56pm Page 640
Part IV Developing with SQL Server
INSTEAD OF triggers are useful when it’s known that the DML statement firing the trigger will always
be rolled back and some other logic will be executed instead of the DML statement. For example:

■ When the DML statement attempts to update a non-updatable view, the
INSTEAD OF trigger
updates the underlying tables instead.
■ When the DML statement attempts to directly update an inventory table, an
INSTEAD OF
trigger updates the inventory transaction table instead.
■ When the DML statement attempts to delete a row, an
INSTEAD OF trigger moves the row to
an archive table instead.
The following code creates a test
INSTEAD OF trigger and then attempts to INSERT arow:
CREATE TRIGGER dbo.TriggerTwo ON dbo.Person
INSTEAD OF INSERT
AS
PRINT ‘In the Instead of Trigger’;
go
INSERT dbo.Person(PersonID, LastName, FirstName, Gender)
VALUES (51, ‘Ebob’, ‘’,‘M’);
Result:
In the Instead of Trigger
(1 row(s) affected)
The result includes the INSTEAD OF trigger’s ‘‘hello world’’ declaration and a report that one row was
affected. However, selecting
personID 51 will prove that no rows were in fact inserted:
SELECT LastName
FROM dbo.Person
WHERE PersonID = 51;
Result:
LastName


(0 row(s) affected)
The INSERT statement worked as if one row were affected, although the effect of the INSERT statement
was blocked by the
INSTEAD OF trigger. The PRINT command was executed instead of the rows being
inserted. In addition, the
AFTER trigger is still in effect, but its PRINT message failed to print.
Trigger limitations
Given their nature (code attached to tables), triggers have a few limitations. The following SQL
commands are not permitted within a trigger:
640
www.getcoolebook.com
Nielsen c26.tex V4 - 07/23/2009 4:56pm Page 641
Creating DML Triggers 26
■ CREATE, ALTER,orDROP database
■
RECONFIGURE
■ RESTORE database or log
■
DISK RESIZE
■ DISK INIT
Disabling triggers
A user’s DML statement can never bypass a trigger, but a system administrator can temporarily
disable it, which is better than dropping it and then recreating it if the trigger gets in the way of a
data-modification task.
Disabling a trigger can only be done for the entire database, not just for the current
connection or the current user; this makes disabling a trigger an extremely dangerous
instrument. Think twice before making any attempt to bypass an instrument that is used to guard data
integrity!
To temporarily turn off a trigger, use the ALTER TABLE DDL command with the ENABLE TRIGGER or
DISABLE TRIGGER option:

ALTER TABLE schema.TableName ENABLE or DISABLE TRIGGER
schema.TriggerName;
For example, the following code disables the INSTEAD OF trigger (TriggerOne on the Person
table):
ALTER TABLE dbo.Person
DISABLE TRIGGER TriggerOne;
To view the enabled status of a trigger, use the OBJECTPROPERTY() function, passing to it the object
ID of the trigger and the
ExecIsTriggerDisabled option:
SELECT OBJECTPROPERTY(
OBJECT_ID(’TriggerOne’),’ExecIsTriggerDisabled’);
Listing triggers
Because triggers tend to hide in the table structure, the following query lists all the triggers in the
database based on the
sys.triggers catalog view:
SELECT Sc.name + ‘.’ + Ob.name as [table],
Tr.Name as [trigger],
CASE (Tr.is_instead_of_trigger )
WHEN 0 THEN ‘after’
WHEN 1 THEN ‘instead of’
END AS type,
CASE (Tr.is_disabled)
WHEN 0 THEN ‘enabled’
WHEN 1 THEN ‘disabled’
641
www.getcoolebook.com