How to get table_name in a trigger - SQL Server

Question

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

Answer 1

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