SQL Server 2005 history tables using triggers

Question

I have a table and want to insert the current values of records that are being updated or deleted.

I tried to create a trigger on before update and on before delete but SQL Server 2005 doesn't like the before word.

How do I get this to work?

I want the trigger to take the current record and copy it to a history table that is a mirror of the base table with the two added fields of dateBackedUp (that takes a getDate()) and a field called action (that takes the action 'updated' or deleted')

please help

Answer 1

You don't need to use a before keyword. You can create a trigger as normal and then select from the deleted table which gets created when a trigger is..er...triggered.

eg

create trigger Audit
on tblCustomers
after update, delete

insert into tblAudit
select * from deleted;

(untested as I don't have a SQL Server here, but if doesn't work then the BOL entry for Create Trigger should tell you about the deleted table)

Answer 2

create TRIGGER [update_Project] ON Project
FOR UPDATE
AS
INSERT project_history 
SELECT * FROM deleted

using the deleted table works as it gives me the data that will be updated. the inserted table provides the new values... not the ones i want to log.

Answer 3

SQL Server doesn't have a concept of BEFORE INSERT or BEFORE DELETE. These kind of audit operations really don't need to be executed before the action, most of the time - so just use the AFTER INSERT and AFTER UPDATE triggers that you already have.

using the deleted table works as it gives me the data that will be updated. the inserted table provides the new values... not the ones i want to log.

When you do an AFTER UPDATE trigger, the Inserted pseudo-table contains the new values - and the Deleted table contains the old values. So for the AFTER UPDATE trigger, you can access both pseudo tables and get both the old and the new values.

The AFTER INSERT trigger only has access to Inserted pseudo table which contains the values that were inserted.

The AFTER DELETE trigger only has access to the Deleted pseudo table which contains the values of the rows that were deleted.