Wednesday, 1 February 2017

Sql Grab the SQL Command/Statement that fired a Trigger

Hi All, here a easy script to Grab the SQL Command/Statement that fired a Trigger:

--LWEBCODE
--Website Building & free script at http://lwebcode.blogspot.com/

CREATE TRIGGER trgUpdate
ON User_Master
FOR UPDATE

AS
--Print ('AFTER Trigger [trgUpdate] – Trigger executed !!')
--DBCC INPUTBUFFER(@@spid)

--Before Check if not exists
CREATE TABLE TMP_CMD ( [eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(4000))
DECLARE @sSql nvarchar(4000)
SET @sSql = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO TMP_CMD EXEC(@sSql)


This works but has a big problem, it can’t grab more than 4000 characters , to solve this  there are SQL “Magic Table” which stores last row edited by query
To clone this row you can use:


SELECT * INTO TMP_INSERTED FROM INSERTED
SELECT * INTO TMP_DELETED FROM DELETED


and you can inspect affected row.

1 comment:

  1. How do I make money from playing games and earning
    These ford escape titanium are the three 바카라사이트 most popular forms of gambling, and are explained in a worrione.com very nba매니아 concise and concise manner. The most common forms of gambling are: หาเงินออนไลน์

    ReplyDelete