Hi, we want to create an audit table for research for a short time using a trigger. But in the trigger we want to be able to trap the calling proc, or trigger name that inserted the record into the table. We did some research and found @PROCID() which seems to work, but if a trigger is insesrting the record then we dont know. Is there anyway to find out what trigger name without hardcoding it, is firing the insert..? I thought about hitting sysprocesses to get that but not sure if that will work. I hope that makes sense. So in the example below there is an audit table, and the column "Calling_Item" we want to be the name of what fired it. Anyone know how to do that..? Create Table dbo.ProspectsAudit (TableId INT IDENTITY, application VarChar(100), host VarChar(50), actionDate DateTime, username VarChar(20), Comment VarChar(50), Calling_Item Varchar(50), duplicate INT ) -thanks. -David Roesch San Diego, Ca
You are on the right track using @@PROCID. See if this helps: USE tempdb CREATE TABLE t (c1 INT) GO CREATE TRIGGER testme ON dbo.t FOR INSERT AS DECLARE @tablename SYSNAME DECLARE @triggername SYSNAME SELECT @tablename = OBJECT_NAME(parent_obj), @triggername = [name] FROM sysobjects WHERE id = @@procid SELECT @tablename AS Tablename, @triggername AS Triggername GO INSERT INTO t SELECT 1 DROP TABLE t -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs
