trigger on Create table to capture table name and schema

Question

Hi,

I am trying to create a Database trigger that will fire on a create table statement and enable CDC on that table:

Create TRIGGER [Tr_CreateNewTable]  
ON DATABASE  
FOR CREATE_TABLE  
AS  
BEGIN  
 DECLARE @tablename varchar(300)    
 DECLARE @schemaname VARCHAR(300)

 EXEC sys.sp_cdc_enable_table  
 @source_schema = @schemaname,  
 @source_name   = @tablename,  
 @role_name     = NULL,  
 @supports_net_changes = 0  
END 

But I an unsure how to get the name schema of the table being created into variables @tablename &@schemaname

Any ideas how to accomplish this?

Answer 1

You need to use EVENTDATA():

https://learn.microsoft.com/en-us/sql/t-sql/functions/eventdata-transact-sql?view=sql-server-ver15

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

Answer 2

That does the trick!

Thanks!