Creating logfunctionality

Hello!

Does anyone have a link to some good information about creating log functionality for SQL-server. Basically I want to track changes made to a table, through an update-trigger. I would like to store both the old values and the new values (is this possible?), in a new table.

Thanks!

/MagnusI don't have a link for you but this is easy to do...

First readup on triggers in the Books on line. Checkout the following keywords:

"triggers, programming"
"triggers, multirow"
"triggers, inserted tables"
"triggers, last trigger"

In the trigger, you will want to select rows from the temporary memory-resident "inserted" or "deleted" tables. the "inserted" table holds the new stuff and the "deleted" table holds the old stuff.

For an insert you would select just from the "inserted" table and insert this into the new part of the audit table.

For an Update write a select to join the "deleted" table to the "inserted" table for the before and after stuff. Again, the "deleted" table is the old and the "inserted" table is the new. You will need to have well defined keys for this to work right.

For a delete, you guessed it, select data from the "deleted" table and insert the results into the old part of the audit table.

Remember that in SQL Server the triggers fire AFTER the Insert/Update/Delete. If you are using multipule triggers I would make this the last trigger to fire so you don't have to roll back your audit if something fails your business logic.

Post back if you have more questions.