Create a T-SQL Database-Level Trigger:
Create Trigger tr_DDLOnTablesUnderReplication
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
--Select * from sys.triggers order by name
DECLARE @TABLE_NAME SYSNAME
SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')
If Exists (Select 1
FROM
[Distribution].[dbo].[MSarticles] AS A
INNER JOIN [Distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id])
where a.article = @TABLE_NAME
and A.[source_owner] = CONVERT(nvarchar(100), CURRENT_USER)) --DBO
Begin
PRINT @TABLE_NAME + ' - Is a Replicated Table. Please contact your DBA to proceed!'
ROLLBACK;
End
END
Note: This code assumes your Distribution DB is named Distribution and on the same server as your Subscribers. If not the case,
adjust the T-SQL accordingly.
Testing:
Table not configured for Replication:
Alter table dbo.TBL1 add PhotoID int NUll
Output:
Command(s) completed successfully.
Table configured for Replication:
Alter table dbo.TBL2 add PhotoID int NUll
Output:
TBL2 - Is a Replicated Table. Please contact DBA Sysadmin to proceed!
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.
DBA steps to make schema change:
Step 1:
Disable Trigger [tr_TBL2_upd_Status] on [dbo].[TBL2]
go
Step 2:
Make schema change
Step 3:
Enable Trigger [tr_TBL2_upd_Status] on [dbo].[TBL2]
go
|
|
|
|
|
|