Thanks for the compliment.

On auditing inserts, if you audit before-and-after, there's no need to audit inserts. If you just audit changes (inserted table), you need to log the insert, but don't need to log deletions other than that they happened.

Let's say you insert a row that's exactly 1000 bytes wide, and you change it 10 times, then delete it. It stays 1000 bytes during each update.

Before-and-After Auditing, without Insert, with Delete: 21,000 bytes of log data

After-only Auditing, with Insert, Delete timestamp-only: 11,008 bytes of log data

If you use the XML logging I suggested to someone yesterday, which only logs the colums that actually change, you can end up with much smaller log tables.

For example, this:

set nocount on

--drop table loggingtest

--drop table loggingtest_log

go

create table LoggingTest (

ID int identity primary key,

Col1 char(100),

Col2 char(100),

Col3 char(100),

Col4 char(100),

Col5 char(100),

Col6 char(100),

Col7 char(100),

Col8 char(100),

Col9 char(100),

Col10 char(100))

go

create table LoggingTest_Log (

XActionID int identity primary key,

LogDate datetime not null default (getdate()),

LogBy varchar(100) not null default(system_user),

Data XML)

go

create trigger LoggingTest_LogXML on dbo.LoggingTest

after insert, update, delete

as

set nocount on

/*

NullIf used on inserted and deleted, because it reduces the size of the

resulting XML data. XML, by default, ignores null values and doesn't

include the column.

*/

declare @XML xml

select @XML =

(select isnull(i.ID, d.ID) as ID,

rtrim(nullif(cast(d.Col1 as varchar(100)), cast(i.Col1 as varchar(100)))) as Col1_From,

rtrim(nullif(cast(i.Col1 as varchar(100)), cast(d.Col1 as varchar(100)))) as Col1_To,

rtrim(nullif(cast(d.Col2 as varchar(100)), cast(i.Col2 as varchar(100)))) as Col2_From,

rtrim(nullif(cast(i.Col2 as varchar(100)), cast(d.Col2 as varchar(100)))) as Col2_To,

rtrim(nullif(cast(d.Col3 as varchar(100)), cast(i.Col3 as varchar(100)))) as Col3_From,

rtrim(nullif(cast(i.Col3 as varchar(100)), cast(d.Col3 as varchar(100)))) as Col3_To,

rtrim(nullif(cast(d.Col4 as varchar(100)), cast(i.Col4 as varchar(100)))) as Col4_From,

rtrim(nullif(cast(i.Col4 as varchar(100)), cast(d.Col4 as varchar(100)))) as Col4_To,

rtrim(nullif(cast(d.Col5 as varchar(100)), cast(i.Col5 as varchar(100)))) as Col5_From,

rtrim(nullif(cast(i.Col5 as varchar(100)), cast(d.Col5 as varchar(100)))) as Col5_To,

rtrim(nullif(cast(d.Col6 as varchar(100)), cast(i.Col6 as varchar(100)))) as Col6_From,

rtrim(nullif(cast(i.Col6 as varchar(100)), cast(d.Col6 as varchar(100)))) as Col6_To,

rtrim(nullif(cast(d.Col7 as varchar(100)), cast(i.Col7 as varchar(100)))) as Col7_From,

rtrim(nullif(cast(i.Col7 as varchar(100)), cast(d.Col7 as varchar(100)))) as Col7_To,

rtrim(nullif(cast(d.Col8 as varchar(100)), cast(i.Col8 as varchar(100)))) as Col8_From,

rtrim(nullif(cast(i.Col8 as varchar(100)), cast(d.Col8 as varchar(100)))) as Col8_To,

rtrim(nullif(cast(d.Col9 as varchar(100)), cast(i.Col9 as varchar(100)))) as Col9_From,

rtrim(nullif(cast(i.Col9 as varchar(100)), cast(d.Col9 as varchar(100)))) as Col9_To,

rtrim(nullif(cast(d.Col10 as varchar(100)), cast(i.Col10 as varchar(100)))) as Col10_From,

rtrim(nullif(cast(i.Col10 as varchar(100)), cast(d.Col10 as varchar(100)))) as Col10_To,

case

when i.ID is not null and d.ID is not null then 'U'

when i.ID is null then 'D'

else 'I'

end as Act

from inserted i

full outer join deleted d

on i.id = d.id

for xml auto)

insert into dbo.LoggingTest_Log (Data) -- Rest of log columns are filled by defaults

select @xml;

go

insert into dbo.loggingtest (col1, col2, col3, col4, col5, col6, col7, col8,

col9, col10)

select 'a','a','a','a','a','a','a','a','a','a'

update dbo.loggingtest

set col1 = '1'

update dbo.loggingtest

set col1 = '2'

update dbo.loggingtest

set col1 = '3'

update dbo.loggingtest

set col1 = '4'

update dbo.loggingtest

set col1 = '5'

update dbo.loggingtest

set col1 = '6'

update dbo.loggingtest

set col1 = '7'

update dbo.loggingtest

set col1 = '8'

update dbo.loggingtest

set col1 = '9'

update dbo.loggingtest

set col1 = '10'

delete from dbo.loggingtest

The primary table shows as .008 Meg of data, and the log table ends up at .008 Meg too, even after a logged insert, 10 logged updates, and a logged delete. That's kind of an artificial case, because of the minimal size of the updates, but it does illustrate the point.

So, depending on your method of logging, logging inserts may or may not matter much. With this XML log, it does matter, and can/should be omitted. With an "after-only" log, it's needed.

(If you want to modify that XML trigger to not log inserts, just change it from a Full Outer Join to a Right Outer Join, and modify the Case statement.)