I've recently been tasked with exporting data from one of our databases to a central Data Warehouse. The export is supposed to be incremental since a full load every 24 hours would be a massive waste of resources, bandwidth and time.
And therein lies my question, what is the best way to catch both new rows as well as changed rows in the database? I've seen recommendations on everything from Timestamps to creating a DateTime column and update it manually on each insert/update, and now I'm at a complete loss.
If I were to go with the DateAndTime column, I figure that there MUST be a way to create for instance a trigger or similar to catch all inserts and updates, or am I wrong?
I know that this question isn't quite as down to the nitty gritty as many other questions, but I'm really stuck and will appreciate all help and/or hints of where to look. Thanks in advance. :)