Enabling CDC on a table in a DDL Trigger

Question

I am attempting to disable\enable CDC in a DDL trigger like so:

ALTER TRIGGER trgTableAlter ON DATABASE FOR	 ALTER_TABLE AS BEGIN SET NOCOUNT ON; DECLARE @EventData XML = EVENTDATA(), @schemaname varchar(255) = '', @tablename nvarchar(255), @tableid bigint = NULL

	SELECT @tablename = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')

	--get schema for table don't assume dbo
	SELECT @schemaname = TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tablename

	SET @tableid = NULL;
	SET @tableid = object_id(@schemaname + '.' + @tablename)

	--verify table has cdc enabled. if so then disable\enable
	IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = @tableid AND is_tracked_by_cdc = 1)
	BEGIN
		DECLARE @old_capture_instance sysname,  @indexname varchar(255), @rolename varchar(255)

		SELECT @old_capture_instance = capture_instance, 
		@indexname = index_name, @rolename = role_name 
		FROM cdc.change_tables WHERE source_object_id = @tableid

		BEGIN
			EXEC sys.sp_cdc_disable_table
				@source_schema = @schemaname, 
				@source_name = @tablename, 
				@capture_instance = @old_capture_instance;
		END

		BEGIN
			EXEC sys.sp_cdc_enable_table
				@source_schema = @schemaname,
				@source_name = @tablename,
				@role_name = @rolename,
				@index_name = @indexname,
				@supports_net_changes = 1, 
				@capture_instance = 'Test1'
		END

		

	END
END

After setting up the trigger and attempt to alter a CDC enabled table I get the following error Msg 22832, Level 16, State 1, Procedure sys.sp_cdc_enable_table_internal, Line 673 [Batch Start Line 10]

Could not update the metadata that indicates table [dbo].[bHQUM] is enabled for Change Data Capture. The failure occurred when executing the command 'insert into [cdc].[index_columns]'. The error returned was 3930: 'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.'. Use the action and error to determine the cause of the failure and resubmit the request.
Msg 3609, Level 16, State 2, Line 11
The transaction ended in the trigger. The batch has been aborted.

Is it simply not possible to enable and disable CDC in a DDL trigger? Any ideas?

Thanks

Answer 1

First, keep in mind that a trigger always fire in the context of a transaction defined by the statement that fired the trigger, even if there is a user-defined transaction.

The part

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.'.

Suggests that sp_cdc_enable_table has trapped an error with TRY-CATCH, but then tried to continue, despite that the transaction was doomed. Which it always will be when you are in a trigger. (And doomed means exactly what the error message says: you must rollback the transaction.) Possibly you could use Profiler with the event Error:Exception enabled to see what error that may be occurring prior to this error.

I note an error in the trigger:

SELECT @tablename = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')

	--get schema for table don't assume dbo
	SELECT @schemaname = TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tablename

But you have the name MyTable in two schemas, which schema will you get?

Instead use:

SELECT @tablename = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
       @schemaname = @EventData.value('(/EVENT_INSTANCE/SchematName)[1]',  'NVARCHAR(255)')

By the way, what does SELECT @@version report?