Sql-server – MS SQL Express – Find when a Query was run

sql serversql server 2014sql-server-express

I have an ecommerce website in .NET and using MS SQL Server Express, Version info:

Microsoft SQL Server 2014 – 12.0.4100.1 (X64) Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation Express Edition (64-bit) on
Windows NT 6.3 (Build 9600: ) (Hypervisor)

There is a DELETE query running from my code and I can not figure out what is triggering it. (My entire system shows one place that has that Delete code, but that can not be the problem.)

I need to find out from the database when the Delete is running so I can try to use that Data to help me solve my problem.

Even if I can't find going backwards, I would like to install/configure something to help me find it in the future, as it is a recurring problem

Best Answer

Here is an example of creating a delete trigger to capture the results of the delete

--demo setup
DROP TABLE IF EXISTS TestDelete;
DROP TABLE IF EXISTS TestDeleteSave;

--create a TestDelete table and a TestDeleteSave
--the TestDeleteSave table will hold the contents of deleted rows
--from TestDelete when the trigger is fired

CREATE TABLE TestDelete (Id int, Description varchar(50));
CREATE TABLE TestDeleteSave (Id int, Description varchar(50));
go

--insert sample row into TestDelete
INSERT INTO TestDelete (
    Id
    ,Description
    )
VALUES (
    1
    ,'Test Description'
    )
go

--create a delete trigger on the TestDelete table.
--the 'deleted' virtual table will contain the rows that were deleted
--during a transaction
CREATE TRIGGER [dbo].[TestDelete_Delete] ON [dbo].[TestDelete]
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

INSERT INTO TestDeleteSave
SELECT *
FROM deleted

END;
GO

ALTER TABLE [dbo].[TestDelete] ENABLE TRIGGER [TestDelete_Delete]
GO

--now, delete a row from the TestDelete table which will fire the delete trigger
delete from TestDelete where id = 1

--now, select affected rows from the TestDeleteSave table
select * from TestDeleteSave
Related Question