Auditing user actions ( DML ) on a table !

Please find below the scripts that I have created to implement solution for logging DML user actions(Insert,Update and Delete) on a particular table.

I have thoroughly tested them and its error-free.( This works on sql server 2000,2005 and 2008 )

Step-1) Create a sample table called "Table1"

CREATE TABLE [dbo].[table1](
[id] [bigint] NULL
)

Step-2) Create a table called "Magic" to hold the user actions performed on the table "Table1"

create table magic
(
i bigint primary key identity(1,1),
action_taken varchar(25),
id bigint not null,
username varchar(50),
rw datetime default(getdate())
)

Step-3) Create a Trigger to fire upon Insert,Update or Delete operations on table "Table1"

create trigger trig1_table1
on table1
for insert,update,delete
as
begin
set nocount on

declare @in bigint
declare @de bigint
select @in=count(*) from inserted
select @de=count(*) from deleted
if(@in=@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'updated',i.id,current_user
from inserted i
end
else if(@in>@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'inserted',i.id,current_user
from inserted i
end
else if(@in<@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'deleted',d.id,current_user
from deleted d
end

end

Step-4) Simulate the environmental values and know the results by fetching * from table "Magic"

insert into table1
values(258)

insert into table1
values(100)

update table1
set id=347
where id=258

select * from table1

delete from table1
where id=100

select * from magic--Magic is the table that has the log information of all the DML actions performed on the table called "Table1"