APP_NAME() empty when deleting

3 May 2007 9:53 PM

Jon Davis

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

5 May 2007 2:16 AM

Dan Guzman

>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

Show quoteHide quote

"Jon Davis" <j**@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
>
>