Auditing a complex mix of reasonably small tables

I plan to use the following approach to audit the tables that represent user-editable configuration of an automated system (over the course of system's life these will be inevitably extended in their numbers and stuffed with more columns). This approach will not be applied to main data tables as those are automatically updated.

SQL Server 2005+, based on this. Suggestions?

```
CREATE TABLE [dbo].[audit] (
[id] [bigint] IDENTITY(1,1) NOT NULL,
[time] [datetime] NOT NULL,
[username] nvarchar NOT NULL,
[useraddress] nvarchar NULL,
[tablename] nvarchar NULL,
[operation] AS (case when [oldvalue] IS NULL then 'I' when [newvalue] IS NULL then 'D' else 'U' end),
[oldvalue] [xml] NULL,
[newvalue] [xml] NULL
)
GO

ALTER TABLE [dbo].[audit] ADD CONSTRAINT [DF_audit_time] DEFAULT (sysutcdatetime()) FOR [time]
GO

CREATE PROCEDURE [dbo].[save_audit]
@old_values xml,
@new_values xml,
@tablename nvarchar(128)
AS
BEGIN
SET NOCOUNT ON;

IF @old_values IS NOT NULL OR @new_values IS NOT NULL
BEGIN
DECLARE
@username nvarchar(128),
@useraddress varchar(48)

SELECT @username = original_login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID
SELECT @useraddress = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID

INSERT INTO audit (username, useraddress, tablename, oldvalue, newvalue)
VALUES (@username, @useraddress, @tablename, @old_values, @new_values)
END
END
GO

CREATE TRIGGER [dbo].[audit_tablename] ON [dbo].[tablename]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @i xml, @d xml, @t nvarchar(128)
SET @i = (SELECT * FROM inserted FOR XML AUTO)
SET @d = (SELECT * FROM deleted FOR XML AUTO)
SET @t = (SELECT OBJECT_NAME(parent_object_id) FROM sys.objects WHERE object_id = @@PROCID)
EXEC save_audit @d, @i,

Looks Like a nice, straight forward solution.

The only suggestion that I have , and while this is an unlikely situation, if the database were hosted on a failover cluster and the user were to connect to the instance locally (i.e. from anyone of the servers in the cluster), you wouldn't know which computer they logged in from. The value of @useraddress would be set as ''.

This would also be the case when a user connects locally to the SQL Server instance, but if there's only one instance (i.e. not in a cluster), then it's not really a big deal, since you know which computer that is.

Although, to play it safe, why not modify the code to set the value for @useraddess like this, which will give you the computer name instead of '':

DECLARE @useraddress nvarchar(128); --'
                     ,CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)))
  FROM sys.dm_exec_connections
 WHERE session_id = @@SPID;

See:

• SERVERPROPERTY (Transact-SQL)

StackExchange Code Review Q#15760, answer score: 2