How to get table_name in a trigger - SQL Server
Solution 1
Solution:
@tablename = OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE sys.objects.name = OBJECT_NAME(@@PROCID)**
Regards,
Christian
Solution 2
I tweaked @ChrisRun answer a little bit to include schema of the trigger in case You would have same trigger names in different schemas.
@tablename = SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE sys.objects.name = OBJECT_NAME(@@PROCID)
AND SCHEMA_NAME(sys.objects.schema_id) = OBJECT_SCHEMA_NAME(@@PROCID)
All the best, Mike
ChrisRun
My prime interests are web development in ASP.NET, EPiServer, but also database administration and application development using primary C#.
Updated on June 12, 2022Comments
-
ChrisRun almost 2 years
I've created a trigger which task is to store information about the trigger-events. For example: "New employee added to the table on date 2014-10-13.
I've created a table - Audit - which stores all the information (from the trigger).
CREATE TABLE [dbo].[Audit]( [id] [int] IDENTITY(1,1) NOT NULL, [tableName] [nvarchar](255) NOT NULL, [auditData] [nvarchar](max) NULL, [userName] [nvarchar](255) NOT NULL, PRIMARY KEY (id))
However, the trigger I've created looks like this:
CREATE TRIGGER [dbo].[tr_Actor_ForInsert_Audit] ON [dbo].[Actor] FOR INSERT AS BEGIN DECLARE @userName NVARCHAR(255) DECLARE @tableName NVARCHAR(255) = 'Actor' DECLARE @name VARCHAR(255) DECLARE @birthdate DATE SELECT @userName = SYSTEM_USER SELECT @name = name FROM inserted SELECT @birthdate = birthdate FROM inserted INSERT INTO Audit VALUES (@tableName, 'New ' + LOWER(@tableName) + ' with Name = ' + @name + ' and Birthdate = ' + CONVERT(NVARCHAR,@birthdate) + ' was added at ' + CONVERT(NVARCHAR,GETDATE()), @userName)
END;
As you can see, the variable userName is initialized to SYSTEM_USER. but the variable tableName is intitialized to a hard-coded value .
Question: Is there any possible way to somehow generically initialized the variable tableName to the tableName the same way I did for userName?
For example, if something like this existed:
@tableName = SYSTEM_TABLE_WHERE_TRIGGER(TRIGGERNAME)_EXIST
Regards,
Christian