Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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?
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)'),
That does the trick!
Thanks!