How to log any activity

Dear friends,
I'm working on an entity relationship diagram about a mice farm.
Let's say we have a very very basic diagram like this:

MOUSE "is in" CAGE

For example, this gives

Mouse "n1500" is in cage "AAA"
Mouse "n1501" is in cage "BBB"

One operator can take one mouse and put it into another cage, for
example the first line becomes

Mouse n1500 is in cage CCC

Now, I was asked to track any movement... I ask myself: do I need to
add

OPERATOR "moves" MOUSE

so that I have

Operator "John" moves Mouse "n1500" on "Monday 3rd, 2007" at "5 PM"
from "AAA" to "CCC"?

or this kind of logging is provided some way by SQL server?

Thanx a lot for any hint.Not automatically.

You could add a trigger and an audit table to your database, so that every time a record is inserted updated, the pertinent data would be logged to the audit table.

ref Using TRIGGERS to add an audit trail to a table (http://www.xtremevbtalk.com/showthread.php?t=217047)

Note that the example shown doesn't audit the data which was changed, but that is easy to add.

Note: Since you're asking specifically about SQL Server, I'm moving this thread there (to the SQL Server Forum).