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
FOR DROP_TABLE, ALTER_TABLE
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
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
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)','nvarchar(100)')
PRINT 'Altering object :' + @object
IF @Object = @ObjectName
PRINT 'Alter schema not allowed on this database - disable the DDL trigger'
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.