How to get sql statement in trigger

Question

I have insert, update, delete triggers for every tables to logging actions. I am retrieving before and after datas from deleted, inserted and wrapping these into xml. But some logs can't show before and update values. My sql statement is:

USE [cop]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Delete]   ON  [dbo].[Seanslar]   
    AFTER DELETE 
AS 
BEGIN 

SET NOCOUNT ON 

DECLARE @deleted AS XML 
SET @deleted = (select * from deleted for xml AUTO, ELEMENTS XSINIL)

DECLARE @logIslem TINYINT 
SET @logIslem = 3 

DECLARE @tableName VARCHAR(200) 
SELECT @tableName = OBJECT_SCHEMA_NAME( parent_id ) + '.' + OBJECT_NAME( parent_id ) 
  FROM sys.triggers 
 WHERE object_id = @@PROCID  

DECLARE @xmlToChar NVARCHAR(MAX)
SET @xmlToChar = CAST(@deleted AS nvarchar(MAX))

IF LEN(@xmlToChar)<10 
BEGIN 
    IF EXISTS(select * from deleted)
        select @xmlToChar = CAST(seans_id AS NVARCHAR(MAX)) from deleted
    ELSE
        SET @xmlToChar = 'Deleted is empty!'
END

DECLARE @allXml AS XML  
SET @allXml = '<'+@tableName+'>'+ @xmlToChar +'</'+@tableName+'>'  

INSERT INTO [dbo].[Logla]
    ([logIslem], [trgKullanici_id], [tabloAdi], [logXml])     
VALUES           
    (@logIslem, SUSER_NAME(), @tableName, @allXml)

  END

Is there any way to learn "sql statement" executed inside trigger?

RBarryYoung · Accepted Answer · 2012-11-26 16:22:49Z

0

There is no practical way to capture the executing SQL statement text inside of a DML Trigger fired by that statement.

You can do this with a DDL (metadata) Trigger, but not a DML (normal) Trigger.

And yes, there are one or two very impractical ways to do it, but I really do not recommend them unless:

You are very, very SQL proficient, and
You really, really need to get it, and
You can afford a lot of development and testing time

answered Nov 26, 2012 at 16:22

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get sql statement in trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related