Using Triggers - Enforce Schema Integrity Among Objects on Different Servers or Databases
(Page 4 of 4 )
I showed you earlier how DDL triggers can be used to audit and prevent changes on database objects. They could also be used to maintain a database schema. In the following example, a large table is horizontally partitioned into quarterly tables (to make management easier). Each table should store sales information about sales in one quarter. Therefore, there are four tables that should be maintained identically:
Create database Quarterly
Go
Use Quarterly
Go
Create table Sales1(
PartId int,
CustomerId int,
SalesDate smallint,
Sales float)
Go
Create table Sales2(
PartId int,
CustomerId int,
SalesDate smallint,
Sales float)
Go
Create table Sales3(
PartId int,
CustomerId int,
SalesDate smallint,
Sales float)
Go
Create table Sales4(
PartId int,
CustomerId int,
SalesDate smallint,
Sales float)
Go
After the set of tables is created, we could create a DDL trigger to capture database changes on the Sales1 table and propagate it to other tables. The trigger is based on the Transact-SQL command captured using EventData():
CREATE TRIGGER trdReplicateSalesTableChanges
ON DATABASE
AFTER DDL_TABLE_EVENTS
AS
Print 'trdReplicateSalesTableChanges started.'
declare @event xml
declare @Object sysname,
@ObjectType sysname,
@TSQLCommand nvarchar(max),
@TSQLCommand2 nvarchar(max),
@i int
set @event = EVENTDATA()
set @Object = @event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') 1234567890123456789012345678901234567890 123456789012345678901234567890
set @ObjectType = @event.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)')
set @TSQLCommand = @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
if (@ObjectType = 'TABLE')
begin
if (@Object = 'Sales1')
begin
-- replace Sales1 string with 2-4
Set @i = 2
While @i <= 4
Begin
Set @TSQLCommand2 = REPLACE(@TSQLCommand,'Sales1',
'Sales' + Cast(@i as varchar))
--execute
print @TSQLCommand2
Exec Sp_executeSql @TSQLCommand2
set @i = @i + 1
end
end
end
GO
To test the trigger, we will add a column to the table:
alter table Sales1
add StoreId int null
The trigger will replicate the change to other tables and print the debug information:
trdReplicateSalesTableChanges started. alter table Sales2
add StoreId int null
alter table Sales3
add StoreId int null
alter table Sales4
add StoreId int null
It is justified to leave the print statement in this trigger, since the trigger is designed to be used only during the administrative tasks on table schemas.
NOTE
There may be changes that are too complex and that cannot be propagated correctly this way. You should test results in development and test environment before you use this method in production.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 5 
