Here are some articles I wrote a while back on data auditing:

http://www.sqlservercentral.com/articles/Auditing/63247/

http://www.sqlservercentral.com/articles/Auditing/63248/

There are a number of trigger-based options and samples in the second one.

With regard to a trigger to record updates and inserts, what you need to do in the query part of the trigger is select from the "inserted" or "deleted" table.

Here's how I like to do trigger-based audits these days:

USE ProofOfConcept;

GO

IF OBJECT_ID(N'dbo.MyTable001') IS NOT NULL

DROP TABLE dbo.MyTable001;

IF OBJECT_ID(N'dbo.AuditLog') IS NOT NULL

DROP TABLE dbo.AuditLog;

GO

CREATE TABLE dbo.MyTable001

(ID INT IDENTITY

PRIMARY KEY,

Col1 VARCHAR(100),

Col2 VARCHAR(100));

GO

CREATE TABLE dbo.AuditLog

(ID INT IDENTITY

PRIMARY KEY,

LogTime DATETIME NOT NULL

DEFAULT (GETDATE()),

LogEntry XML);

GO

CREATE TRIGGER dbo.MyTable001_Audit ON dbo.MyTable001

FOR UPDATE, DELETE

AS

SET NOCOUNT ON;

INSERT INTO dbo.AuditLog

(LogEntry)

SELECT (SELECT 'dbo.MyTable001' AS Obj,

*

FROM DELETED

FOR

XML RAW('PriorValues'),

TYPE);

GO

INSERT INTO dbo.MyTable001

(Col1, Col2)

VALUES ('A', 'B'),

('C', 'D');

GO

SELECT *

FROM dbo.AuditLog;

GO

UPDATE dbo.MyTable001

SET Col2 = 'E'

WHERE ID = 2;

GO

SELECT *

FROM dbo.AuditLog;

GO