March 26, 2010 at 3:22 am
I have code within a trigger to get the name of the table the trigger has fired on. Here is the trigger:
CREATE TRIGGER tr_Title_IUD ON Person.Title FOR INSERT, UPDATE, DELETE
AS
SELECT P.object_id,
S.name AS SchemaName,
P.name AS TableName
FROM SYS.OBJECTS AS O
JOIN SYS.OBJECTS AS P
ON P.object_id = O.parent_object_id
JOIN SYS.SCHEMAS AS S
ON P.schema_id = S.schema_id
WHERE O.object_id = @@PROCID
This works - I can copy the code to any trigger, and when the trigger fires, the name of the table the trigger fired on is returned.
My question is: is there a better way of doing this? I expect there is a system function like @@TABLENAME or suchlike that I have overlooked.....?
March 26, 2010 at 4:13 am
You have the right basic idea, but look at sys.triggers too.
USE tempdb;
GO
CREATE TABLE dbo.Test (A INTEGER NULL);
GO
CREATE TRIGGER [trg dbo.Test IUD]
ON dbo.Test
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET ROWCOUNT 0;
SET NOCOUNT ON;
SELECT TAB.name
FROM sys.triggers TRG
JOIN sys.tables TAB
ON TAB.[object_id] = TRG.parent_id
WHERE TRG.[object_id] = @@PROCID;
END;
GO
INSERT dbo.Test DEFAULT VALUES;
GO
DROP TABLE dbo.Test;
Note that I am returning data from a trigger just for demonstration purposes - triggers should never return results (and the ability to do so is in final deprecation).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply