October 16, 2006 at 1:10 pm
Is it possible to capture delta (i.e. changed records including deleted records) for a specific table? or to capture all the DML queries executed against a table?
thanks
October 17, 2006 at 12:12 am
Hi
Depends on the tables you are working with do they contain a field with a modified date? If not and you are working with MS SQL you could look at using triggers but this can get a bit messy.
Other than that your other choice would be to move the data to a staging table then check what type of record it is an take the correct action.
Regards
Mike
October 17, 2006 at 1:10 am
A simple approach would be a trigger (and a historytable with the same structure as the maintable, and with an extra field containing the deleteddate) like this:
CREATE TRIGGER <triggername> ON <maintable>
FOR UPDATE, DELETE
AS
insert into <maintable_Hist> select *, getdate() from deleted
rgds.
Peter
October 17, 2006 at 4:30 am
Peter,
Thats the perfect solution to a post I had here last week. I didn't realise that the tables could be different structure (additional fields). Adding the Current_timestamp and system_user tracks Who and When.
many thanks
Richie
October 17, 2006 at 7:10 am
unfortunately tirgger is not an option. I am surprised that in SQL Server there is no way for me to capture DML executed against a specific object.
October 17, 2006 at 8:08 am
You can also think of creating trace, filtering to an object you want to monitor changes, capturing t-sql and stored proc to the statement level.
Save the output to a file and then use the function
fn_trace_gettable to read the content, apply filter and then save to the destination table.
This way you can get SQL Statement, who modified your objects and so many properties..
Ignas
October 17, 2006 at 9:50 am
i wonder if there is a way to read the transaction log and filter needed dml out of it?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply