How to identify which process updated a table

CREATE TABLE YourLogTable(ModificationTime DATETIME, [Application] NVARCHAR(500), [SQL] NVARCHAR(MAX))
GO

CREATE TRIGGER [dbo].[trgUpdateLogger] ON [dbo].[YourTable]
FOR INSERT,UPDATE,DELETE
AS
--Get updating process info
CREATE TABLE #s(EventType VARCHAR(30), Parameters INT, SQL NVARCHAR(MAX))
INSERT INTO #s
EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')

--Write to log table
INSERT INTO YourLogTable (ModificationTime, [Application], [SQL])
SELECT GETDATE(), APP_NAME(), #s.SQL FROM #s

--Cleanup
DROP TABLE #s
GO


Note: I found it on LinkedIN... thought it might be useful

Comments

  1. manub22August 5, 2015 at 4:31 AM

    similar to my response on MSDN TSQL forum: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/346c2eab-2a3f-4ff1-a876-d29a67f9c670/how-to-identify-which-process-updated-a-table-resolved?forum=transactsql

    ReplyDelete

Post a Comment

Popular posts from this blog

SysDictField object not initialized. - Views

One of my Colleague had this issue.. I’m using a View (with some calculated fields), as the source for another View (summed fields, so I’m working with “total” records). This 2 nd View continues to have this error message: SysDictField object not initialized. Stack trace (C)\Classes\SysTableBrowser\AddField - line 32 (C)\Classes\SysTableBrowser\showAutoFields - line 95 (C)\Classes\SysTableBrowser\run - line 67 Solution: Go to the Field Groups and delete the fields that are in the Autoreport and delete the fields that are Autoreport and not in the Field List of the View.
Read more

How to filter data for the last few weeks?

I got a task where I had to get the data for the last few weeks excluding the current week. The week begins on a Sunday and ends on a Saturday. Here is the code: { Date dateFrom; Date dateTo; date fd; ; fd = dateStartWk(systemDateGet()); // get the First day of the Week dateTo = fd - 2; // Subtracted 2 since the first day is a Monday dateFrom = dateTo - ((5 * 7) -1); // shud be 3*7 - 1 as ToDate value is also Included info(strFmt("%1-%2-%3", fd, dateTo, dateFrom)); }
Read more

Get the value of Month or YY in SysComputedCOlumns in AX views

Here is the Code: public static server str WFinvoiceDateMM() {     str sDate =         SysComputedColumn::returnfield(             tableStr (WFSyscoInvoicesV),             queryDataSourceStr (WFSyscoInvoicesQ, CustInvoiceJour),             fieldStr (CustInvoiceJour, InvoiceDate)             );     return 'Month(' + SysComputedColumn::fromUtcDateToDate(sDate) + ')' ; }
Read more