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
Best Answer
DB_NAME()
will always return the context of the trigger, not the context of the event. But you're in luck: theEVENTDATA()
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).