|
|
|
|
|
|
by iheartmemcache
3841 days ago
|
|
|
I've done it one of two ways - via VBA and via VSTO Add-ins. My VBA version has been carefully crafted to emulate a pseudo-ORM based off INFORMATION_SCHEMA and all, making it pretty portable. It works fairly well because what usually ends up happening is say Partner Foo will have his read-write to his schema and it will all be segmented up via catalog views and other rules. There are the standard concurrency issues (you have to discuss with your client whether you want him to take a mutex on the result set, or potentially allow stale data) so it's fairly naive in that regard. The upside of it it's all self-contained (i.e., not a pile) and portable because you effectively get "reflection" with any sufficiently decent RDBMS impl. Maybe 10-15% of that VBA has to be tweaked based on their business requirements (defining behavior like if pri_row gets deleted in table foo by user bar, do you offer a prompt to cascade delete[1]?) In some situations [such as a cascade delete], the business requirements need the Stakeholder or PM or TL or whomever to authorize certain events due to internal corporate governance policies. At that point, the VSTO add-in route is taken because usually there has to be integration with other governance/workflow software (one of the subsidiaries for Blue Cross/Blue Shield I worked with was notoriously bad re: pushing the buck and over-complicating workflows). Anyways, the MSVS sln I use with that is pretty much the opposite of a VBA pile. [1] There's almost never any destructive DELETE's taken, rather a boolean active flag is toggled, and a new row is inserted with a more recent timestamp so you can '...order by timestamp desc limit 1'. That way if auditors come in, not only do they have the full audit log but we can reconstruct state at any given time. Either way, a naive VBA implementation that doesn't appeal to system tables for all the schema info can easily be written (in a maintainable fashion, as maintainable as VBA goes at least) in under a week. |
|
|
This is the most detailed explanation I found regarding the VBA method: http://www.toadworld.com/platforms/sql-server/w/wiki/10392.e...
And the company you linked originally gives away a generic VSTO add-in: https://www.ablebits.com/excel-addins.php#sql-server
The part I was missing was when you said 'audit tables [and] audit trails' (etc.)... it sounds like this would all be build-it-yourself.