question

zoeOhara-0533 avatar image
0 Votes"
zoeOhara-0533 asked zoeOhara-0533 answered

trigger on Create table to capture table name and schema

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?

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

You need to use EVENTDATA():

https://docs.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)'),
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

zoeOhara-0533 avatar image
0 Votes"
zoeOhara-0533 answered

That does the trick!

Thanks!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.