SQL Server 2016 – How to Log SQL Statements Deleting Records

It could be a mechanism within MS Access when it comes to the linked table. How ???

MSAccess sees a linked table and you issue this:

DELETE FROM [mytable];

This will not run over to SQL Server and do a deletion of all records in a single transaction. What MSAccess does is pick up every PRIMARY KEY and issues an individual DELETE FROM [mytable] WHERE pk = ???, probably as individual transactions (explains the delay before the confirm). I remembered this wonky behavior going back 15 years because I saw this happen when having linked tables to SQL Server, MySQL, and Oracle.

If this is still what MS Access does to a linked table, you are better off connecting to SQL Server via isql and issuing the DELETE on the server side, especially if there are a lot of rows to delete. At the very least, you should minimize executing bulk operations against SQL Server from MSAccess.

Although I have never worked with it, MSAccess can allow "pass through" queries where you issue a SQL Command and the ODBC Driver simply passes the query to the Server Side for parsing and execution rather that trying to parse and execute the SQL the "ODBC" way.

If things have changed over the years with linked tables, I'll leave it to the SQL Server community to address this if I am way off base.

A partial answer to your question, could be to use DBCC INPUTBUFFER, this doesn't necessarily give you the stored procedure that is deleting your records. It can give you the first stored procedure called by the client, ie:

application code calls:

EXEC EntryStoredProc  /* this call is the one that will be identified by DBCC INPUTBUFFER */
...
/* inside EntryStoredProc */
EXEC SecondaryProc
...
/* inside SecondaryProc */
DELETE FROM [MyTable] ...

To add the logging to your trigger, add code similar to:

DECLARE @EventInfo varchar(200)
DECLARE @EventSource TABLE (EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000)) 

INSERT INTO @EventSource
EXEC ('dbcc inputbuffer (' + @@spid + ') with no_infomsgs')

SELECT TOP 1 @EventInfo = SUBSTRING(APP_NAME() + '.' + ISNULL(EventInfo, ''), 0, 200) 
FROM @EventSource

INSERT INTO MyLogTable (ID, EventInfo)
SELECT d.ID, @EventInfo
FROM deleted

Note: This obviously has a performance impact and should not be left on your live server for a long time.