You could write a custom trigger. This is not something I do but here is an example of an audit on the accthist table.
if exists (select * from sysobjects where id = object_id('xAcctHistAuditTable'))
DROP TABLE xAcctHistAuditTable
GO
CREATE TABLE xAcctHistAuditTable(
-- standard audit fields
audit_log_id uniqueidentifier DEFAULT NEWID(),
audit_user sysname DEFAULT SUSER_SNAME(),
audit_changed datetime DEFAULT GETDATE(),
NT_User char (50),
hostname char(256),
program_name char (50),
sqltext varchar(4000),
-- additional fields
cpnyid char(10),
acct char(10),
sub char(24),
ledgerid char(10),
fiscyr char(4)
-- add your additional fields below
)
if exists (select * from sysobjects where id = object_id('xAcctHistAuditTrigger'))
DROP TRIGGER xAcctHistAuditTrigger
GO
CREATE TRIGGER xAcctHistAuditTrigger
ON Accthist
FOR update
AS
set nocount on
--TEMP TABLE TO RECORD SQL STATEMENT OF PROCESS FIRING THIS TRIGGER
declare @CMD varchar(8000)
CREATE TABLE #SQLBUFFER (EVENTTYPE VARCHAR(255), SPID SMALLINT, EVENTINFO VARCHAR(4000))
SELECT @CMD = 'DBCC INPUTBUFFER('+ CONVERT(VARCHAR,@@SPID) +') WITH NO_INFOMSGS'
INSERT #SQLBUFFER EXEC (@CMD)
if (select COUNT(*) from master.dbo.sysprocesses where spid = @@SPID and program_name not like '%01520%' and program_name not like '%01560%') > 0
begin
-- Audit NEW record.
INSERT INTO xAcctHistAuditTable
(NT_User,hostname,program_name,sqltext,CpnyID, Acct, Sub, LedgerID, FiscYr)
-- add additional fields to the list above
SELECT
-- standard fields
(select left(ltrim(nt_username),50) from master.dbo.sysprocesses where spid = @@SPID),
(select left(ltrim(hostname),255) from master.dbo.sysprocesses where spid = @@SPID),
(select left(ltrim(program_name),50) from master.dbo.sysprocesses where spid = @@SPID),
(SELECT EVENTINFO FROM #SQLBUFFER),
CpnyID, Acct, Sub, LedgerID, FiscYr
FROM inserted
end
-- End of Trigger
go
