Trigger with a Variable Select and Order by Is there a way to a set trigger that will activate once a new table is created and will get the new tables name?
I was able to come up with the query below but it only gets the first query by alphabetical order, not by date created

CREATE TRIGGER CreateTable
ON DATABASE
    FOR CREATE_TABLE
AS
     DECLARE
       @table varchar(max);
     SELECT  top 1  @table =   [name] FROM sys.tables 
     insert into NewTable values(@table)
     ;
     end
You don't look at sys.tables for that... even if you got the last one created, on a busy system, how would you know that someone else didn't create one more recently? With TOP comes ORDER BY so the following would likely work most of the time, but it is a completely bad idea:

SELECT TOP (1) @table = name FROM sys.tables ORDER BY create_date DESC;


No, instead, you need to extract the table name from EVENTDATA():

CREATE TRIGGER CreateTable
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    DECLARE
      @sql       nvarchar(MAX),
      @EventData xml = EVENTDATA();

    ;WITH x(s,o) 
    AS
    (
      SELECT
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'nvarchar(128)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'nvarchar(128)')
    )
    INSERT dbo.NewTable(ColumnName)
      SELECT QUOTENAME(s) + N'.' + QUOTENAME(o)
      FROM x;
END
GO