trigger on Create table to capture table name and schema

asked 2021-04-05T13:55:39.433+00:00
zoe Ohara 281 Reputation points

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?

No comments
{count} votes

1 additional answer

Sort by: Most helpful
  1. answered 2021-04-05T14:57:01.643+00:00
    zoe Ohara 281 Reputation points

    That does the trick!

    Thanks!

    No comments