Nielsen c27.tex V4 - 07/23/2009 8:27pm Page 662
Part IV Developing with SQL Server
Developing DDL Triggers
ThecodeinthetriggerisnormalT-SQLcode.Insome way, a DDL trigger is easier to write than a DML
trigger because DDL triggers always fire for a single event, whereas DML triggers must handle events
with multiple affected rows involving the base table, and the
inserted and deleted virtual tables.
The complexity of the DDL trigger results from the fact that the data about the event is in XML.
EventData()
DDL triggers can respond to so many different events that they need some method of capturing data
about the event that caused them to fire. DML triggers have the
inserted and deleted virtual tables;
DDL triggers have the
EventData() function. This function returns XML-formatted data about the
event. The XML schema varies according to the type of event captured. Note that parts of the XML
schema are case sensitive.
Using the
EventData() function to populate an XML variable, the trigger can use XQuery to investi-
gate the values. Use the XQuery
Value() method to extract the data from the XML.
The XML schema for event data is at
C:\Program Files\Microsoft SQL Server\100\Tools\
Binn\schemas\sqlserver\2006\11\events\events.xsd
Alternatively, the event schema is published at />The following code example creates a DDL trigger that reads
EventData() into an XML variable and
then selects from the variable to display the data:
CREATE TRIGGER DDLTrigger
ON DATABASE
FOR CREATE_TABLE
AS
Set NoCount ON

DECLARE @EventData XML = EventData()
SELECT
@EventData.value
(’data(/EVENT_INSTANCE/SchemaName)[1]’,’VARCHAR(50)’) as
‘Schema’,
@EventData.value
(’data(/EVENT_INSTANCE/ObjectName)[1]’, ‘VARCHAR(50)’) as
‘Object’,
@EventData.value
(’data(/EVENT_INSTANCE/EventType)[1]’, ‘VARCHAR(50)’) as
‘EventType’
With the DDL triggers in place, the next comamnd creates a table, which fires the trigger, which
examines
EventData’s XML, and returns the values to the client:
CREATE TABLE Test (Col1 INT)
662
www.getcoolebook.com
Nielsen c27.tex V4 - 07/23/2009 8:27pm Page 663
DDL Triggers 27
Result:
Schema Object EventType

dbo Test CREATE_TABLE
F
or more on XML and working with XQuery, see Chapter 18, ‘‘Manipulating XML Data.’’
Preventing database object changes
DDL triggers can execute code, including executing a transaction rollback command. Such a trigger
could prohibit anyone from making server- or database-level changes.
The following code is a simple example of a rollback DDL trigger blocking any stored procedures from
being altered in the database:

CREATE TRIGGER NoTouchDaProc
ON DATABASE
FOR ALTER_PROCEDURE, DROP_PROCEDURE
AS
Set NoCount ON
Raiserror (’These Procs may not be altered or dropped!’,16,1)
Rollback
To test the DDL trigger, the next few commands attempt to modify the procedure so it won’t print
‘‘SQL Rocks!’’:
DROP PROC QuickProc
Result:
Msg 50000, Level 16, State 1, Procedure NoTouchDaProc, Line 6
These Procs may not be altered or dropped!
Msg 3609, Level 16, State 2, Procedure QuickProc, Line 3
The transaction ended in the trigger. The batch has been aborted.
And
ALTER PROC QuickProc
AS
Print ‘Oracle Rocks!’
Result:
Msg 50000, Level 16, State 1, Procedure NoTouchDaProc, Line 6
These Procs may not be altered or dropped!
Msg 3609, Level 16, State 2, Procedure QuickProc, Line 3
The transaction ended in the trigger. The batch has been aborted.
663
www.getcoolebook.com
Nielsen c27.tex V4 - 07/23/2009 8:27pm Page 664
Part IV Developing with SQL Server
W
ith DDL triggers, you can write your own system to prevent object changes that disagree with your

shop’s policies, but a more strategic solution might be to use SQL Server 2008’s new policy-based
management feature, documented in Chapter 40, ‘‘Policy-Based Management.’’
Summary
DDL triggers provide a safety net — a way to track every change to the schema. The event model that
can be tracked is comprehensive and the data available to the trigger using the
EventData() function
and XML is dynamic and complete. Without a doubt, DDL triggers serve their purpose well.
Highlights from this chapter are as follows:
■ Server-level DDL triggers can trap any event and are seen in Object Explorer under the Server
Objects ➪ Triggers node.
■ Database-level DDL triggers exist in the user database, can only fire for database-level events,
and are listed in Object Explorer in the [Database] ➪ Programmability ➪ Database Triggers
node.
■ DDL Triggers can fire for any specific DDL event, or for DDL Event Groups — a hierarchy of
DDL events.
■ Because DDL triggers can fire for such a broad range of events, the
EventData() function
returns XML data about the event.
The next chapter continues the theme of developing with SQL Server with the critical concepts of build-
ing out the abstraction layer.
664
www.getcoolebook.com
Nielsen c28.tex V4 - 07/23/2009 4:58pm Page 665
Building Out the Data
Abstraction Layer
IN THIS CHAPTER
Buying database extensibility
Building CRUD stored
procedures
Searching stored procedures

I
recently blogged the question, ‘‘Why use stored procedures?’’ (http://
tinyurl.com/ohauye
) and received a firestorm of replies. I invite
you to add your view to the replies — let’s attempt the most replies on
SQLBlog.com.
My post is based on the discussion of extensibility presented in Chapter 2, ‘‘Data
Architecture,’’ and makes the point that the abstraction layer should be as perma-
nent as the data it encapsulates. The only effective data abstraction layer is T-SQL.
One of the talks I give at conferences is ‘‘7 SQL Server development practices
more evil than cursors.’’ What’s the number one worst development practice on
my list? Ad-hoc SQL, because it violates the abstraction layer and creates a brittle
database.
There are many good reasons for wrapping the database in a protective layer of
stored procedures:
■ Extensibility: It’s far easier to modify the database when there’s a
consistent contract to access the database.
■ Usability: It’s far easier for application developers to call a set of stored
procedure API calls that return the correct result set than for them to
write correct SQL queries.
■ Integrity: The goal is to get the correct answer to the question. Stored
procedures written by database developers will include better queries
than ad-hoc SQL written by application developers.
■ Performance: Moving the lookups and validation closer to the data
improves performance.
■ Security: The best security (tighter control, reduced surface area, limits
SQL injection) passes all database access through stored procedures and
blocks any direct access to the tables.
665
www.getcoolebook.com

Nielsen c28.tex V4 - 07/23/2009 4:58pm Page 666
Part IV Developing with SQL Server
The basic idea is that a contract, or API agreement, drives development on both the database and
the client side. If the database developers want to refactor the database to increase performance or
integrity or add new features that don’t change the API, then they are free to do so without affecting
any other code. If the application needs a new feature that affects the API, then both sides need to agree
on the change and can work on the modification independently until both are ready for integration
testing.
CRUD Stored Procedures
Generally speaking, the data abstraction layer needs an interface for each table for each of the following
tasks:
■ Searching for multiple rows
■ Fetching a single row
■ Inserting new data
■ Updating existing data
■ Deleting existing data
Each of these stored procedures should include data validation, transaction control, and error handling.
For lookup tables, some choose to build a more dynamic solution that allows a single set of stored pro-
cedures to work against multiple tables by making the table name a parameter and dynamically building
the
FROM clause within the stored procedure.
For examples of these stored procedures, please download the latest version of the
OBXKites sample database from www.sqlserverbible.com.
I’m also considering developing an AutoCRUD utility that will code-gen CRUD stored procedures. If
you’d like to use such a utility, please e-mail me at

Best Practice
W
hen designing the data abstraction layer, avoid using a CRUD matrix approach — CRUD being a list
of create, retrieve, update, and delete functions for every table. A data abstraction layer — that is,

a set of sprocs for every table — will tend to lock in the schema to that set of sprocs. Instead, design the
data abstraction layer as a set of logical contracts that deal with business entities and tasks, even though
the contract may involve multiple underlying tables.
For example, design a single interface that involves the inventory, order, and shipping tables. Decreasing
the inventory count for an item might involve updating the count field, triggering a reorder, and updating a
physical capacity table. You want to ensure that one contract does it all.
666
www.getcoolebook.com
Nielsen c28.tex V4 - 07/23/2009 4:58pm Page 667
Building Out the Data Abstraction Layer 28
Google-Style Search Procedure
Of the standard data abstraction layer stored procedures, the most interesting one is the search stored
procedure. This Google-style search comes from Nordic (New Object/Relational Design) version 2.09 —
my CodePlex.com project that transforms SQL Server into an object database. It works very well even
against millions of rows. Of course, this is a work in progress, so please get the newest version from
CodePlex.com or SQLServerBible.com.
The goal of the Google-style search is to enable users to enter as many search words as desired in any
order and then find the best results. I’ve had a lot of fun designing this search routine.
The common solution is to use dynamic SQL to generate a complex
WHERE clause. This solution instead
splits the
@SearchString words entry into a set-based list and stores the search words in a table vari-
able. The table variable is then joined with several data sources — names, object code, and a list of data
extracted from searchable columns. Each time a row is found it’s added to the #
Results table. As the
row is found multiple times, its
hits counter is incremented. At the end, the rows with the most hits
are sorted to the top of the list. This search also handles class, workflow state, and association filters,
which are shown toward the end of the listing:
Create

alter
PROC SearchObjects
(@ClassID INT,
@StateID INT = NULL,
@SearchString VARCHAR(500) = NULL,
@AssocMMID INT = NULL,
@HASAssoc BIT = 0)
WITH recompile
AS
CREATE TABLE #Results (
ObjectID INT NOT NULL PRIMARY KEY ,
Hits TINYINT NULL
);
CREATE TABLE #Classes (ClassID INT)
INSERT #Classes
SELECT ClassID
FROM dbo.SubClassesID(@ClassID)
DECLARE @SQL NVARCHAR(MAX)
SET NoCount ON
IF @SearchString = ‘’
SET @SearchString = NULL

All in class search
667
www.getcoolebook.com
Nielsen c28.tex V4 - 07/23/2009 4:58pm Page 668
Part IV Developing with SQL Server
IF @SearchString IS NULL AND @StateID IS NULL
INSERT #Results (ObjectID, Hits)
SELECT top(1000) o.ObjectID, 1

FROM Object o
JOIN #Classes c
ON o.ClassID = c.ClassID
LEFT JOIN dbo.State s
ON o.StateID = s.StateID
WHERE o.ClassID IN (SELECT ClassID FROM
dbo.SubClassesID(@ClassID))

All in class / state search
IF @SearchString IS NULL AND @StateID IS NOT NULL
INSERT #Results (ObjectID, Hits)
SELECT top(1000) o.ObjectID, 1
FROM Object o
JOIN #Classes c
ON o.ClassID = c.ClassID
JOIN dbo.State s
ON o.StateID = s.StateID
WHERE o.StateID = @StateID

save search string
IF @SearchString IS NOT NULL
BEGIN
DECLARE @User SYSNAME
SET @User = SUSER_SNAME()
upsert
UPDATE dbo.SearchHistory
SET LastViewDate = GETDATE()
WHERE SearchString = @SearchString
AND [User] = @User
IF @@RowCount = 0

rewrite as MERGE
INSERT dbo.SearchHistory ([User], SearchString, LastViewDate)
VALUES (@User, @SearchString, GETDATE())

Object Code Exact match / first word entry /
regardless of class / state / associations / anything
IF Exists(SELECT * FROM dbo.Object WHERE ObjectCode = @SearchString)
BEGIN
EXEC SearchObjects_ObjectCode @SearchString
RETURN
END
668
www.getcoolebook.com
Nielsen c28.tex V4 - 07/23/2009 4:58pm Page 669
Building Out the Data Abstraction Layer 28

Parse out multiple search words
DECLARE @SearchWords TABLE
(Word VARCHAR(50))
INSERT @SearchWords (Word)
SELECT String
FROM dbo.String2Set(LTRIM(RTRIM(@SearchString)))
Exact Name2 Match - 1 points
should be a merge
INSERT #Results (ObjectID, Hits)
SELECT ObjectID, 1
FROM @SearchWords W
JOIN dbo.Object O
ON O.Name2 = W.Word
JOIN #Classes C

ON O.ClassID = C.ClassID
GROUP BY ObjectID
Exact Name1 Match - 1 points
MERGE #Results AS R
USING (
SELECT ObjectID , COUNT(*) as Hits
FROM @SearchWords W
JOIN dbo.Object O
ON O.Name1 = W.Word
JOIN #Classes C
ON O.ClassID = C.ClassID
GROUP BY ObjectID
)asS
ON R.ObjectID = S.ObjectID
WHEN Matched
THEN UPDATE
SET Hits += R.Hits
WHEN NOT MATCHED BY TARGET
THEN INSERT (ObjectID, Hits)
VALUES(ObjectID, 1);
Name1 Soundex - 1 point
MERGE #Results AS R
USING (
SELECT ObjectID
FROM @SearchWords W
JOIN dbo.Object O
ON O.Name1Soundex = Soundex(W.Word)
JOIN #Classes C
ON O.ClassID = C.ClassID) as S
669

www.getcoolebook.com
Nielsen c28.tex V4 - 07/23/2009 4:58pm Page 670
Part IV Developing with SQL Server
ON R.ObjectID = S.ObjectID
WHEN Matched
THEN UPDATE
SET Hits = R.Hits + 1
WHEN NOT MATCHED BY TARGET
THEN INSERT (ObjectID, Hits)
VALUES(ObjectID, 1);
Name2 Soundex - 1 point
MERGE #Results AS R
USING (
SELECT ObjectID
FROM @SearchWords W
JOIN dbo.Object O
ON O.Name2Soundex = Soundex(W.Word)
JOIN #Classes C
ON O.ClassID = C.ClassID) as S
ON R.ObjectID = S.ObjectID
WHEN Matched
THEN UPDATE
SET Hits = R.Hits + 1
WHEN NOT MATCHED BY TARGET
THEN INSERT (ObjectID, Hits)
VALUES(ObjectID, 1);
Exact Seachable Column Match - 1 point per hit
MERGE #Results AS R
USING (
SELECT ObjectID, COUNT(*) as Hits

FROM SearchWordList SWL
JOIN @SearchWords W
ON SWL.Word = W.Word
JOIN #Classes C
ON O.ClassID = C.ClassID
GROUP BY ObjectID) as S
ON R.ObjectID = S.ObjectID
WHEN Matched
THEN UPDATE
SET Hits = R.Hits + S.Hits
WHEN NOT MATCHED BY TARGET
THEN INSERT (ObjectID, Hits)
VALUES(ObjectID, 1);
END @SearchString IS NOT NULL

apply filters
IF @StateID IS NOT NULL
670
www.getcoolebook.com
Nielsen c28.tex V4 - 07/23/2009 4:58pm Page 671
Building Out the Data Abstraction Layer 28
DELETE #Results
WHERE ObjectID IN
(SELECT ObjectID FROM dbo.Object WHERE StateID <> @StateID)
IF @AssocMMID IS NOT NULL AND @HASAssoc = 0
DELETE #Results
WHERE ObjectID NOT IN
(SELECT ObjectID
FROM Association A
JOIN AssociationMatrix AM

ON A.AssociationMatrixID = AM.AssociationMatrixID
JOIN dbo.ClassStateAssociationMatrix CSAM
ON AM.AssociationMatrixID = CSAM.AssociationMatrixID
WHERE AM.AssociationMatrixMasterID = @AssocMMID)
IF @AssocMMID IS NOT NULL AND @HASAssoc = 1
DELETE #Results
WHERE ObjectID IN
(SELECT ObjectID
FROM Association A
JOIN AssociationMatrix AM
ON A.AssociationMatrixID = AM.AssociationMatrixID
JOIN dbo.ClassStateAssociationMatrix CSAM
ON AM.AssociationMatrixID = CSAM.AssociationMatrixID
WHERE AM.AssociationMatrixMasterID = @AssocMMID)
Return Results
SELECT TOP 1000 o.ObjectID, c.ClassID, c.ClassName, s.StateID,
s.StateName, o.ObjectCode, o.Name1, o.Name2,
‘(’ + Cast(r.Hits as VARCHAR(9)) + ‘) ’
+ IsNull(dbo.SearchDetails(o.ObjectID),’’) AS Descript,
o.Created, o.Modified,
o.Version
FROM #Results r
JOIN dbo.Object o
ON r.ObjectID = o.ObjectID
JOIN dbo.Class c
ON o.ClassID = c.ClassID
LEFT JOIN dbo.State s
ON o.StateID = s.StateID
WHERE r.Hits > (Select max(Hits) /2 FROM #Results)
ORDER BY r.Hits DESC, ObjectCode

RETURN
The real point of this procedure isn’t the cool way it searches multiple locations for multiple words, but
that the API is very stable. I have refactored the search stored procedure dozens of times, sometimes
even changing the table structures, without ever having to alter the .NET client code.
671
www.getcoolebook.com