ptg
974
CHAPTER 30 Creating and Managing Triggers
update the data related to the jobs table
UPDATE jobs
SET jobs.min_lvl = i.min_lvl,
jobs.max_lvl = i.max_lvl,
jobs.job_desc = i.job_desc
FROM inserted i
WHERE jobs.job_id = i.job_id
AND (jobs.min_lvl <> i.min_lvl
OR jobs.max_lvl <> i.max_lvl
OR jobs.job_desc <> i.job_desc)
update the data related to the employee table
UPDATE employee
SET employee.job_lvl = i.min_lvl
FROM inserted i
WHERE employee.emp_id = i.emp_id
GO
A section in Listing 30.13 checks the fields related to the jobs table and updates the base
table if any of the values have changed. Another section updates the employee table for
the employee fields that have been changed in the view.
NOTE
You could enhance the trigger in Listing 30.13 to include logic to check for specific
updates or to update only those employees who are assigned to the job and have a job
level below the new minimum. These enhancements are not included in the listing to
keep the example simple.
If you now execute the same UPDATE statement, you don’t get an error message:
UPDATE employeeJobs
SET min_lvl = 40,
job_lvl = 40

WHERE job_id = 12
GO
The following results show values selected from the employeeJobs view after the update is
executed successfully:
min_lvl max_lvl job_id job_desc job_lvl emp_id

25 100 14 Designer 35 ENL44273F
25 100 14 Designer 89 PSA89086M
Download from www.wowebook.com
ptg
975
Using DML Triggers
25 100 14 Designer 100 KFJ64308F
25 100 13 Sales Representative 35 PMA42628M
25 100 13 Sales Representative 64 CGS88322F
25 100 13 Sales Representative 100 TPO55093M
40 100 12 Editor 40 Y-L77953M
40 100 12 Editor 40 H-B39728F
40 100 12 Editor 40 HAS54740M
Notice that the Editor job now has a minimum level (min_lvl) equal to 40 and that all
the employees who have that job level (job_lvl) are also set to 40.
You can see the added flexibility that you get by using the INSTEAD OF trigger on a basic
view. This flexibility is also applicable to a more sophisticated view called a distributed
partitioned view. With this type of view, data for the view can be partitioned across differ-
ent servers. Partitioning this way enables you to scale a database solution and still have a
single view of the data that appears as one table.
You can make data modifications via a distributed partitioned view, but some restrictions
exist. If you do not meet the requirements for updating the view, you can use the
INSTEAD
OF trigger to bypass these restrictions; this is similar to adding an INSTEAD OF trigger on a

nonpartitioned view.
INSTEAD OF Trigger Restrictions
INSTEAD OF triggers have many capabilities, but they also have limitations. Following are
some of them:
. INSTEAD OF triggers do not support recursion. This means they cannot call them-
selves, regardless of the setting of the Recursive Triggers database option. For
example, if an INSERT is executed on a table that has an INSTEAD OF trigger, and the
INSTEAD OF trigger performs an INSERT on this same table, the INSTEAD OF trigger for
this INSERT does not fire a second time. Any AFTER triggers defined on the same
table for INSERT fire based on the INSTEAD OF trigger INSERT.
. You can define only one INSTEAD OF trigger for each action on a given table.
Therefore, you can have a maximum of three INSTEAD OF triggers for each table: one
for INSERT, one for UPDATE, and one for DELETE.
. A table cannot have an INSTEAD OF trigger and a FOREIGN KEY constraint with CASCADE
defined for the same action. For example, you cannot have an INSTEAD OF trigger
defined for DELETE on a given table as well as a foreign key with a CASCADE DELETE
definition. You get an error if you attempt to do this. In this situation, you could have
INSTEAD OF triggers defined on INSERT and UPDATE without receiving errors.
30
Download from www.wowebook.com
ptg
976
CHAPTER 30 Creating and Managing Triggers
Using DDL Triggers
DDL triggers were introduced in SQL Server 2005. These triggers focus on changes to the
definition of database objects as opposed to changes to the actual data. The definition of
database objects is dictated by the DDL events that these triggers respond to.
The DDL events that these triggers fire on can be broken down into two main categories.
The first category includes DDL events that are scoped at the database level and affect the
definition of objects such as tables, indexes, and users. The second category of DDL trig-

gers is scoped at the server level. These triggers apply to server objects, such as logins.
The number of DDL events at the database level far exceeds the number at the server
level. Table 30.2 lists the DDL statements and system stored procedures that DDL triggers
can fire on.
TABLE 30.2 DDL Statements and System Stored Procedures
*
Create/Grant/Bind/Add Alter/Update/Deny Drop/Revoke/Unbind
Statements and System Stored Procedures with Database-Level Scope
CREATE_APPLICATION_ROLE ALTER_APPLICATION_ROLE DROP_APPLICATION_ROLE
(sp_addapprole) (sp_approlepassword) (sp_dropapprole)
CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY
ALTER_AUTHORIZATION_
DATABASE (sp_changedbowner)
CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE
CREATE_CONTRACT DROP_CONTRACT
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
CREATE_DEFAULT DROP_DEFAULT
BIND_DEFAULT UNBIND_DEFAULT
GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION
CREATE_EXTENDED_PROPERTY ALTER_EXTENDED_PROPERTY DROP_EXTENDED_PROPERTY
(sp_addextendedproperty) (sp_updateextendedproperty) (sp_dropextended
property)
CREATE_FULLTEXT_INDEX ALTER_FULLTEXT_INDEX
DROP_FULLTEXT_INDEX
(sp_fulltexttable) ( sp_fulltextcatalog) (sp_fulltexttable)
Download from www.wowebook.com
ptg
977
Using DDL Triggers

30
TABLE 30.2 DDL Statements and System Stored Procedures
*
Create/Grant/Bind/Add Alter/Update/Deny Drop/Revoke/Unbind
(sp_fulltext_column)
CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION
CREATE_INDEX ALTER_INDEX DROP_INDEX
(sp_indexoption)
CREATE_MASTER_KEY ALTER_MASTER_KEY DROP_MASTER_KEY
CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME
CREATE_PLAN_GUIDE ALTER_PLAN_GUIDE DROP_PLAN_GUIDE
(sp_create_plan_guide) (sp_control_plan_guide) (sp_control_plan_guide)
CREATE_PROCEDURE ALTER_PROCEDURE DROP_PROCEDURE
CREATE_QUEUE ALTER_QUEUE DROP_QUEUE
CREATE_REMOTE_SERVICE_ ALTER_REMOTE_SERVICE_ DROP_REMOTE_SERVICE_
BINDING BINDING BINDING
RENAME
(sp_rename)
CREATE_ROLE ALTER_ROLE DROP_ROLE
(sp_addrole and
sp_addgroup)
(sp_droprole and
sp_dropgroup)
ADD_ROLE_MEMBER ALTER_ROUTEDROP_ROLE_MEMBER
CREATE_ROUTE ALTER_ROUTE DROP_ROUTE
CREATE_RULE DROP_RULE
BIND_RULE UNBIND_RULE
(sp_bindrule) (sp_unbindrule)

CREATE_SCHEMA ALTER_SCHEMA DROP_SCHEMA
(sp_addrole and sp_adduser) (sp_changeobjectowner)
(sp_addgroup and
sp_grantdbaccess)
Download from www.wowebook.com
ptg
978
CHAPTER 30 Creating and Managing Triggers
TABLE 30.2 DDL Statements and System Stored Procedures
*
Create/Grant/Bind/Add Alter/Update/Deny Drop/Revoke/Unbind
CREATE_SERVICE ALTER_SERVICE DROP_SERVICE
ADD_SIGNATURE DROP_SIGNATURE
CREATE_SPATIAL_INDEX ALTER_INDEX DROP_INDEX
CREATE_STATISTICS UPDATE_STATISTICS DROP_STATISTICS
CREATE_SYMMETRIC_KEY ALTER_SYMMETRIC_KEY DROP_SYMMETRIC_KEY
CREATE_SYNONYM DROP_SYNONYM
CREATE_TABLE ALTER_TABLE DROP_TABLE
(sp_tableoption)
CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER
(sp_settriggerorder)
CREATE_TYPE DROP_TYPE
(sp_addtype) (sp_droptype)
CREATE_USER ALTER_USER DROP_USER
(sp_adduser and
sp_grantdbaccess)
(sp_change_users_login)
(sp_dropuser and
sp_revokedbaccess)
CREATE_VIEW ALTER_VIEW DROP_VIEW

CREATE_XML_INDEX ALTER_INDEX DROP_INDEX
CREATE_XML_SCHEMA_ ALTER_XML_SCHEMA_ DROP_XML_SCHEMA_
COLLECTION COLLECTION COLLECTION
Statements and System Stored Procedures with Server-Level Scope
ALTER_AUTHORIZATION_
SERVER
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
(sp_fulltext_database)
CREATE_ENDPOINT ALTER_ENDPOINTDROP_ENDPOINT
CREATE_EXTENDED_PROCEDURE DROP_EXTENDED_PROCEDURE
(sp_addextendedproc) (sp_dropextendedproc)
ALTER_INSTANCE
Download from www.wowebook.com
ptg
979
Using DDL Triggers
30
TABLE 30.2 DDL Statements and System Stored Procedures
*
Create/Grant/Bind/Add Alter/Update/Deny Drop/Revoke/Unbind
(sp_configure and
sp_addserver)
CREATE_LINKED_SERVER ALTER_LINKED_SERVER DROP_LINKED_SERVER
(sp_addlinkedserver) (sp_serveroption) (sp_dropserver)
CREATE_LINKED_SERVER_
LOGIN
DROP_LINKED_SERVER_
LOGIN
(sp_addlinkedsrvlogin) (sp_droplinkedsrvlogin)
CREATE_LOGIN ALTER_LOGIN DROP_LOGIN

(sp_addlogin and
sp_grantlogin)
(sp_defaultdb and
sp_defaultlanguage)
(sp_droplogin and
sp_revokelogin
(xp_grantlogin and
sp_denylogin)
(sp_password,
sp_change_users_login)
(xp_revokelogin)
CREATE_MESSAGE ALTER_MESSAGE DROP_MESSAGE
(sp_addmessage) (sp_altermessage) (sp_dropmessage)
CREATE_REMOTE_SERVER ALTER_REMOTE_SERVER DROP_REMOTE_SERVER
(sp_addserver) (sp_setnetname) (sp_dropserver)
GRANT_SERVER DENY_SERVER REVOKE_SERVER
ADD_SERVER_ROLE_MEMBER DROP_SERVER_ROLE_MEMBER
Statements and System Stored Procedures with Database-Level Scope
Create/Grant/Bind/Add Alter/Update/Deny Drop/Revoke/Unbind
CREATE_APPLICATION_ROLE ALTER_APPLICATION_ROLE DROP_APPLICATION_ROLE
(sp_addapprole) (sp_approlepassword) (sp_dropapprole)
CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY
ALTER_AUTHORIZATION_
DATABASE(sp_changedbowner)
CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE
CREATE_CONTRACT DROP_CONTRACT
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
CREATE_DEFAULT DROP_DEFAULT
BIND_DEFAULT UNBIND_DEFAULT
Download from www.wowebook.com

ptg
980
CHAPTER 30 Creating and Managing Triggers
TABLE 30.2 DDL Statements and System Stored Procedures
*
Create/Grant/Bind/Add Alter/Update/Deny Drop/Revoke/Unbind
GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE
CREATE_EVENT_ DROP_EVENT_
NOTIFICATION NOTIFICATION
CREATE_EXTENDED_
PROPERTY
ALTER_EXTENDED_PROPERTY DROP_EXTENDED_PROPERTY
(sp_addextendedproperty) (sp_updateextendedproperty) (sp_dropextendedproperty)
CREATE_FULLTEXT_INDEX ALTER_FULLTEXT_INDEX DROP_FULLTEXT_INDEX
(sp_fulltexttable) ( sp_fulltextcatalog) (sp_fulltexttable)
(sp_fulltext_column)
CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION
CREATE_INDEX ALTER_INDEX DROP_INDEX
(sp_indexoption)
CREATE_MASTER_KEY ALTER_MASTER_KEY DROP_MASTER_KEY
CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_
FUNCTION
CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME
CREATE_PLAN_GUIDE ALTER_PLAN_GUIDE DROP_PLAN_GUIDE
(sp_create_plan_guide) (sp_control_plan_guide) (sp_control_plan_guide)
CREATE_PROCEDURE ALTER_PROCEDURE DROP_PROCEDURE
CREATE_QUEUE ALTER_QUEUE DROP_QUEUE
CREATE_REMOTE_SERVICE_BIN
DING

ALTER_REMOTE_SERVICE_BINDIN
G
DROP_REMOTE_SERVICE_BIN
DING
RENAME
(sp_rename)
CREATE_ROLE ALTER_ROLE DROP_ROLE
(sp_addrole and
sp_addgroup)
(sp_droprole and
sp_dropgroup)
ADD_ROLE_MEMBER ALTER_ROUTE DROP_ROLE_MEMBER
Download from www.wowebook.com
ptg
981
Using DDL Triggers
30
TABLE 30.2 DDL Statements and System Stored Procedures
*
Create/Grant/Bind/Add Alter/Update/Deny Drop/Revoke/Unbind
CREATE_ROUTE ALTER_ROUTE DROP_ROUTE
CREATE_RULE DROP_RULE
BIND_RULE UNBIND_RULE
(sp_bindrule) (sp_unbindrule)
CREATE_SCHEMA ALTER_SCHEMA DROP_SCHEMA
(sp_addrole and sp_adduser) (sp_changeobjectowner)
(sp_addgroup and
sp_grantdbaccess)
CREATE_SERVICE ALTER_SERVICE DROP_SERVICE
ADD_SIGNATURE DROP_SIGNATURE

CREATE_SPATIAL_INDEX ALTER_INDEX DROP_INDEX
CREATE_STATISTICS UPDATE_STATISTICS DROP_STATISTICS
CREATE_SYMMETRIC_KEY ALTER_SYMMETRIC_KEY DROP_SYMMETRIC_KEY
CREATE_SYNONYM DROP_SYNONYM
CREATE_TABLE ALTER_TABLE DROP_TABLE
(sp_tableoption)
CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER
(sp_settriggerorder)
CREATE_TYPE DROP_TYPE
(sp_addtype) (sp_droptype)
CREATE_USER ALTER_USER DROP_USER
(sp_adduser and
sp_grantdbaccess)
(sp_change_users_login)
(sp_dropuser and
sp_revokedbaccess)
CREATE_VIEW ALTER_VIEW DROP_VIEW
CREATE_XML_INDEX ALTER_INDEX DROP_INDEX
CREATE_XML_SCHEMA_
COLLECTION
ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_
COLLECTION
Statements and System Stored Procedures with Server-Level Scope
ALTER_AUTHORIZATION_
SERVER
Download from www.wowebook.com
ptg
982
CHAPTER 30 Creating and Managing Triggers
TABLE 30.2 DDL Statements and System Stored Procedures

*
Create/Grant/Bind/Add Alter/Update/Deny Drop/Revoke/Unbind
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
(sp_fulltext_database)
CREATE_ENDPOINT ALTER_ENDPOINT DROP_ENDPOINT
CREATE_EXTENDED_PROCEDURE DROP_EXTENDED_PROCEDURE
(sp_addextendedproc) (sp_dropextendedproc)
ALTER_INSTANCE
(sp_configure and
sp_addserver)
CREATE_LINKED_SERVER ALTER_LINKED_SERVER DROP_LINKED_SERVER
(sp_addlinkedserver) (sp_serveroption) (sp_dropserver)
CREATE_LINKED_SERVER_
LOGIN
DROP_LINKED_SERVER_
LOGIN
(sp_addlinkedsrvlogin) (sp_droplinkedsrvlogin)
CREATE_LOGIN ALTER_LOGIN DROP_LOGIN
(sp_addlogin and
sp_grantlogin)
(sp_defaultdb and
sp_defaultlanguage)
(sp_droplogin and
sp_revokelogin
(xp_grantlogin and
sp_denylogin)
(sp_password,
sp_change_users_login)
(xp_revokelogin)
CREATE_MESSAGE ALTER_MESSAGE DROP_MESSAGE

(sp_addmessage) (sp_altermessage) (sp_dropmessage)
CREATE_REMOTE_SERVER ALTER_REMOTE_SERVER DROP_REMOTE_SERVER
(sp_addserver) (sp_setnetname) (sp_dropserver)
GRANT_SERVER DENY_SERVER REVOKE_SERVER
ADD_SERVER_ROLE_MEMBER DROP_SERVER_ROLE_MEMBER
System stored procedures are enclosed in parentheses.
Download from www.wowebook.com
ptg
983
Using DDL Triggers
30
Triggers created on the DDL events are particularly important for auditing purposes. In
the past, it was very difficult to isolate changes to the definition of a database or to
secure them from change. With DDL triggers, you have the tools necessary to manage
these changes.
Creating DDL Triggers
The basic syntax for creating a DDL trigger follows:
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ , n ] ]
{ FOR | AFTER } { event_type | event_group } [ , n ]
AS { sql_statement [ ; ] [ n ] | EXTERNAL NAME < method specifier > [ ; ] }
The best way to illustrate the use of the DDL trigger syntax and power of these triggers is
to look at a few examples. The example shown in Listing 30.14 illustrates the creation of a
DDL trigger that is scoped at the database level and prevents table-level changes.
LISTING 30.14 A Database-Scoped DDL Trigger for Tables
CREATE TRIGGER tr_TableAudit
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS

PRINT ‘You must disable the TableAudit trigger in order
to change any table in this database’
ROLLBACK
GO
This trigger is fired whenever the CREATE, ALTER,or DROP TABLE statements are executed.
Consider, for example, the following statements that can be run against the
BigPubs2008 database:
ALTER table titles
add new_col int null
alter table titles
drop column new_col
You must disable the TableAudit trigger in order to change any table in this
database
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
These ALTER statements add a column to the titles table and then remove the column.
With the tr_TableAudit trigger in place on the BigPubs2008 database, the error message is
displayed after the first ALTER statement is executed.
Download from www.wowebook.com