You can keep track of the database table changes using
the TRIGGERS. The below table keep track of the Customer table and whenever any
INSERT, DELETE or UPDATE operation takes place it insert the tracking data into
another table. Check out the TRIGGER code below.
ALTER TRIGGER Customer_Activity ON Customer FOR INSERT,DELETE,UPDATE
AS
DECLARE @operationType varchar(100)
DECLARE @customerID int
DECLARE @count int
DECLARE @countDeleted int
DECLARE @table_name varchar(20)
-- get the table name on which the trigger is fired
SELECT @table_name = object_name(parent_obj)
from sysobjects WHERE xtype = 'tr' and name = 'Customer_Activity'
/*
Here I check that whether the rows are
INSERTED, DELETED, UPDATED or no operation is performed
*/
IF EXISTS ( SELECT * FROM inserted ) AND EXISTS ( SELECT * FROM deleted )
BEGIN
SET @operationType = 'UPDATE'
END
IF EXISTS ( SELECT * FROM inserted )
AND NOT EXISTS ( SELECT * FROM deleted )
SET @operationType = 'INSERT'
IF EXISTS ( SELECT * FROM deleted )
AND NOT EXISTS ( SELECT * FROM inserted )
SET @operationType = 'DELETE'
INSERT INTO Operations(OperationType, TableName, DateCreated)
VALUES(@operationType,@table_name,GETDATE())
GO
powered by IMHO 1.3