SQL Server Performance

"Parent" Sql statement causing the TRIGGER?

Discussion in 'General Developer Questions' started by tester, Dec 11, 2006.

  1. tester New Member

    Hi,

    I'd also like to record the sql statement/sproc that causes the INSERT,UPDATE, DELETE trigger in an audit log user table. Would this be technically feasible in Sql 2000?

    I looked at SysCacheObjects table but not sure how to link the SQL info to the current trigger being executing.

    Thanks in advance.
  2. FrankKalis Moderator

    Yes, this is possible in SQL Server 2000:


    USE tempDB
    CREATE TABLE t (c1 INT)
    GO
    CREATE TRIGGER dbo_t ON t FOR INSERT
    AS
    DBCC INPUTBUFFER(@@SPID)
    GO
    CREATE PROCEDURE dbo.getT (@i INT)
    AS
    SET NOCOUNT ON
    INSERT INTO t (c1) VALUES(@i)
    GO

    EXEC dbo.getT 1
    DROP TABLE t
    DROP PROCEDURE dbo.getT

    However there are certain restrictions on the use of DBCC commands. If possible it might be better to add another line to the procs like


    INSERT INTO my_audittable SELECT OBJECT_NAME(@@PROCID)

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. MohammedU New Member

    SysCacheObjects table contains info related "compiled plans" and "execution plans" ect...
    You can't create triggers on system tables...

    You can follow method Frank described but I don't think you get all ddl info using it...

    You can sql trace too get this kind of info...


    Mohammed U.

Share This Page