SQL Server - APP_NAME() empty when deleting

Asked By Jon Davis

03-May-07 05:53 PM

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

04-May-07 10:16 PM

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