DDL trigger to remove the article from the replication when it is altered

Question

Using SQL Server 2008 for Transnational replication.

Some times my team folks altering the table during development before removing the table from subscription in the replication. So it leads to replication failure in the environment. To avoid that, planned to write a DDL trigger which will remove the table from subscription when it is getting altered.

Below is the trigger i have written to achieve the same.

CREATE TRIGGER RemoveArticleFromRepliction ON DATABASE
FOR DROP_TABLE
, ALTER_TABLE AS

BEGIN
SET NOCOUNT ON;

DECLARE @EventData XML = EVENTDATA();
DECLARE @TableName VARCHAR(255) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)')

DECLARE @CMD VARCHAR(255) = 'IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = ''' + @TableName + ''') EXEC sp_mck_DropSubscriptionArticle ''' + @TableName + ''''
EXEC @CMD
END

The trigger applied successfully in SQL server 2008.

Note: sp_mck_DropSubscriptionArticle is a custom procedure. the logic to remove the article from subscription is added in it.

When i execute the alter statement, i am getting the below error

Could not find stored procedure 'IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = 'NOTE') EXEC sp_mck_DropSubscriptionArticle 'NOTE''.

Please help me resolve this issue or suggest me any other approach to fix this problem

Answer 1

Looks like you are missing the brackets after EXEC. Please try the below changed code

CREATE TRIGGER RemoveArticleFromRepliction ON DATABASE
FOR DROP_TABLE
, ALTER_TABLE AS

BEGIN
SET NOCOUNT ON;

DECLARE @EventData XML = EVENTDATA();
DECLARE @TableName VARCHAR(255) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)')

DECLARE @CMD VARCHAR(255) = 'IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = ''' + @TableName + ''') EXEC sp_mck_DropSubscriptionArticle ''' + @TableName + ''''
EXEC (@CMD)
END