SQL Server Performance

Table Audits/Triggers

Discussion in 'General DBA Questions' started by droesch99, Jul 18, 2006.

  1. droesch99 New Member

    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
  2. FrankKalis Moderator

    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

Share This Page