1

I'm creating a Server level trigger that fires after a create table statement, and I want to print the database name and table name.

CREATE TRIGGER LogTempTables
ON ALL SERVER
AFTER CREATE_TABLE
AS 
BEGIN
SET NOCOUNT ON;

       DECLARE @EventData XML = EVENTDATA()
       DECLARE @TableName NVARCHAR(50) 
               = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(50)')
       DECLARE @Database NVARCHAR(50) = DB_NAME()

       PRINT @Database
       PRINT @TableName
END
GO

If I create a table in database ABC123 it prints:

master
TestTable

3

DB_NAME() will always return the context of the trigger, not the context of the event. But you're in luck: the EVENTDATA() contains the object name and the database name. Try adding this to your trigger (also you should be very careful about using 50 characters, because both tables and databases can have longer names).

DECLARE @db sysname = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');
PRINT @db;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.