CREATE TABLE audit(
  [on] datetime not null default getutcdate(),
  [by] varchar(255) not null default system_user+','+AppName(),
  was xml null,
  [is] xml null
)

CREATE TRIGGER mytable_audit ON mytable for insert, update, delete as
INSERT audit(was,[is]) values(
  (select * from deleted as [mytable] for xml auto,type),
  (select * from inserted as [mytable] for xml auto,type)
)

The main goals were to make something really quick to record who did what when across several tables, but it is presumed that this would rarely get used, so queryability wasn't paramount. It would be more likely that tables get created or altered than this stuff would be dived into.

It doesn't work on tables that have certain kinds of fields that are incompatible with FOR XML like TEXT. Had one table with that and I just converted it to nvarchar(max) rather than fight it.

Also, this frees me from having to bloat my tables with fields like CreatedDateTime. Better still, I got to drop fields like DeletedDateTime which implied they shouldn't normally be selected, so every UPDATE and SELECT had to include WHERE DeletedDateTime IS NULL. This really caused problems since I could very easily get primary key violations because a record got 'DELETED' and recreated with the same natural key. I know, I know, just another argument for "surrogate keys only".