We had a request from a developer who wanted to access data from a source database and another database on the same server. We needed to ensure that the underlying tables are not changed in a way that would impact on the view in either database

Prior to 2005 I would have to have done this using a myriad of views and the schemabinding option. Since SQL Server 2005 you can use DDL triggers to fire when certain DDL statements, like CREATE TABLE, ALTER TABLE, DROP TABLE are fired.

DDL triggers focus on changes to the definition of the database schema/object rather than actual data. DDL triggers can be scoped at database level and server level. The server scoped triggers apply to server objects such as logins and the former are database scoped and apply to database objects such as tables and indexes. The following trigger fires for every DROP TABLE or ALTER TABLE execution and stops any table changes being made without first disabling the trigger.

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK

Some may find this a little restrictive and may like to tie the trigger down to a particular table or object...This takes a little more invention:

CREATE TRIGGER Trg_Specific_Object ON DATABASE

FOR alter_table

AS

DECLARE @data XML

SET @data = EVENTDATA()

DECLARE @ObjectName NVARCHAR(100)

SET @ObjectName = 'DDL_Test'

DECLARE @object nvarchar(100)

SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)')

PRINT 'Altering object :' + @object

IF @Object = @ObjectName

BEGIN

PRINT 'Alter schema not allowed on this database - disable the DDL trigger'

ROLLBACK;

END

In the above statement the trigger fires for each ALTER statement BUT it will only rollback the DDL statement if it includes the object specified.