APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>