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

Hirundine

Your trigger is broken - inserted can contain multiple rows (or no rows) so SELECT @name = name FROM inserted isn't going to cut the mustard. Try writing an insert statement that uses inserted in the FROM clause rather than trying to work with scalar variables. – Curnin

Answer 1

Solution:

@tablename = OBJECT_NAME(parent_object_id) 
             FROM sys.objects 
             WHERE sys.objects.name = OBJECT_NAME(@@PROCID)**

Regards,

Christian

Hirundine

Why not use where sys.objects.object_id = @@PROCID rather than matching on name? What if you have more than one table with that name in different schemas? This may give you the wrong one. – Talavera

Answer 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

Impost

I think schema + table name is a unique pair so this is probably safe, but why not use WHERE sys.objects.object_id = @@PROCID? – Talavera

Answer 3

Trigger is intended to a fixed use.However if you want to be dynamic, Call a SP from inside it.

Call Stored Procedure within Create Trigger in SQL Server

Mortify