Logging Amendments via an Update Trigger

[dhqube]

I am currently designing a database and my client requires
that as every change occurs on my main 'customer' table, a log in the way of all the fields from the customer table is created. Thus if the customer table changes 10 times in one year, I will have one current record on the live customer table, and 9 historical records on the customer_log.

I had intended to do this via an update trigger (see syntax below

CREATE TRIGGER customer_update
ON customer
FOR UPDATE
AS

insert log_customer (
log_timestamp,
ID,
[all customer fields])
As
select GETDATE(),
[all customer fields]
from inserted

This works in that it correctly puts a copy of all the fields from customer, & a timestamp , into log_customer, whenever an update to customer is made.

The problem is that the contents of these fields are the customer table AFTER the update has occurred, thus rendering the log process meaningless.

Is there any way of capturing the contents of the customer record BEFORE the change occurs.

I know I can do this via my client (VB6), but I want to do it inside the Server to try and keep as much RI as possible.

Any suggestions gratefully received.

 

[swampBoogie]

the pseudo table deleted contains the old values of the updated records.