I would like to put in place a process into the current oltp databases to facilitate the retrieval of 1) new records inserted; 2) updated records; 3) deleted records (if any).
I have an idea of using triggers to insert into an “audit” table the new, updated, deleted records with the appropriate timestamps on each. There is a caveat to this approach: I am not 100% certain what the previous developers used for retrieving identity. If they have not used the proper method ( i.e. @@Identity ), this can prove problematic to data integrity (e.g. wrong id’s used in a fk reference) especially if the audit tables have their own auto identity.
I would like to know is there any other solution apart from using triggers to implement this so analyze the and propose a strategy to implement a solution.