Trigger with a Variable Select and Order by

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

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

sql-server trigger table

share|improve this question

asked 4 hours ago

Roxzurafa

203

add a comment | 

up vote
1
down vote

favorite

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

sql-server trigger table

share|improve this question

asked 4 hours ago

Roxzurafa

203

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

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

sql-server trigger table

share|improve this question

asked 4 hours ago

Roxzurafa

203

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

sql-server trigger table

sql-server trigger table

share|improve this question

asked 4 hours ago

Roxzurafa

203

share|improve this question

asked 4 hours ago

Roxzurafa

203

share|improve this question

share|improve this question

asked 4 hours ago

Roxzurafa

203

asked 4 hours ago

Roxzurafa

203

asked 4 hours ago

Roxzurafa

203

203

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

up vote
3
down vote



accepted

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

share|improve this answer

answered 4 hours ago

Aaron Bertrand♦

145k19276465

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218094%2ftrigger-with-a-variable-select-and-order-by%23new-answer', 'question_page');

);

Post as a guest

Name Email

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
3
down vote



accepted

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

share|improve this answer

answered 4 hours ago

Aaron Bertrand♦

145k19276465

add a comment | 

up vote
3
down vote



accepted

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

share|improve this answer

answered 4 hours ago

Aaron Bertrand♦

145k19276465

add a comment | 

up vote
3
down vote



accepted

up vote
3
down vote



accepted

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

share|improve this answer

answered 4 hours ago

Aaron Bertrand♦

145k19276465

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

share|improve this answer

answered 4 hours ago

Aaron Bertrand♦

145k19276465

share|improve this answer

share|improve this answer

answered 4 hours ago

Aaron Bertrand♦

145k19276465

answered 4 hours ago

Aaron Bertrand♦

145k19276465

answered 4 hours ago

Aaron Bertrand♦

145k19276465

145k19276465

add a comment | 

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218094%2ftrigger-with-a-variable-select-and-order-by%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email