We have Triggers on almost every table in all the systems we build, every Update re-updates the row itself with a change-date and also every Update/Delete also inserts a row (the "before data") into an Audit table. One audit table per actual table.
We have shopping cart sites where the general public "provide data" - its AN AWFUL LOT OF DATA
Even just moving around the pages on the site they provide data - every single Stored Procedure that they cause to run [by visiting a page] inserts a row in a Logging table, each page view probably triggers 20 - 50 procedures ... they all LOG their parameters etc. ... and then each procedure updates the log row when the procedure exits. It is an AWFUL LOT!! of logging and audit data. The overnight process that deletes stale data takes hours to run ...
... we have never noticed any delay, so I'm surprised that you are expecting to have a problem with that sort of approach.
You don't say if you need to be able to see all changes made to a single row, during the day, or if you just want to know "if it has changed" (and NOT know what the previous data was)
If you only need "has it changed today" I would have a ChangeDateTime column in the row, which is only set if one of the "important" attribute columns changes. You could do that in a trigger (which would catch the change however it was made), or you could do it in the procedure that updates that row (that would be "more efficient" because the row will not be processed once, but anything that updates the table direct would miss that "catcher").
That won't work for Deletes, so to use that method on Deletes you would have to NOT delete the rows, but to FLAG them as deleted instead. Maybe have a process that physically deletes flagged rows after some time (i.e. to allow the "What changed today" report to be run). All existing processes would have to be changed to exclude rows-flagged-as-deleted. Unless you have that already its quite a lot of work to make that change (**)
But personally I would use a Trigger to store the BEFORE data (in just the important Attribute columns, if you like) when the row Updates/Deletes (and optionally, for the Update, ONLY if the data in those important Attribute columns changes)
(**) One way to do that might be to rename the table (e.g. as MyTable_V2) and create a VIEW with the original MyTable name, and have the view SELECT All Columns but with a WHERE clause to exclude records fagged as Deleted.
My expectation is that the clever-trick here will be the way in which old, stale, data is deleted - so that that task does not lock the system, kill the TLogs, and so on. Our Stale Data Delete procedure took a lot of [elapsed] time to write, and perfect. It deletes in batches, backs-off if it detects that the system is busy, figures out if it is filling the TLog and if so triggers additional TLog backups, and so on. We run ours during the night (its a 24/7 system, but nighttime loading is much lighter) but I would have no worry running it during the day because it would automatically run at a slower pace, using smaller batches, when it detected the activity.
Rebuilding indexes and updating statistics may also need some consideration [i.e. to ensure that everything is as efficient as possible]
Maybe @otorre_riversidedpss.org has an alternative solution though, based on newer versions of SQL server - I'm looking forward to hearing that