RE: Not even sure if this is possible???

SSCoach

Points: 16430

June 29, 2014 at 2:29 pm

brisims34 (6/27/2014)
I have been asked to create an auditing trigger on sql server and calls a form (windows, asp.net, or whatever possible) that forces the user on a client workstation to provide a value (reason for changing a value) before the change is committed to the table. Is this possible? I feel like it might be with CLR, but I am not that familiar with it. I would change the App but is compiled and they dont want to ask the vendor to do it because they would have to pay for it. Any help is greatly appreciated as my boss has pretty much already sold this idea to the business users. (argh...)
Thanks!

brisims34 (6/27/2014)
We have an application that is lacking in the audit trailing area for data changes. My boss wants me to create an audit trail to capture who changed the value of a specific column, when and the old and new values from the change. Pretty standard stuff for an audit trail... then he throws the curve ball. He also wants the database to send the user a form and force them to give a reason for the change and if they don't provide a reason then roll back the transaction. If they do then commit the change and log it to the audit trail with the user provided comments as to why they made the change. making matters more difficult the application is compiled and we cant change the code. They dont want to work with the vendor of the application for they will charge us.

Hey there. On a practical level, I can see three possible solutions to this, and none use SQLCLR.

But first: your boss needs to be fired. Seriously. And before anyone goes on thinking, "That isn't helpful. Just answer the question", I would say: it is indeed the most helpful bit of advice here. Your boss "pretty much already sold this idea to the business users" which means he is one of the reasons that the majority of software projects fail. Your boss just promised a bunch of people something that happens to be probably one of the dumbest and most dangerous ideas related to database programming ever said out loud. Hold up a transaction? Really? By a back-end service that has no concept of a display context, no less. I am almost certain that this is not possible in SQLCLR, even with an Assembly set to UNSAFE. And it shouldn't be. SQL Server responds to requests; it does not make requests (see footnote below). Assuming for a moment that this is somehow possible, here is why you shouldn't do this:

What if the user walks away from their computer and doesn't respond for 30 minutes while they are on break? What other processes are being blocked or deadlocked during this time? What data in the system is considered stale at the time the person does respond and it can commit since dependent data in other tables might have changed during that time.

What happens when a SQL Job updates the table?

Do you know all of the business logic and data dependencies within the schema? Are you going to audit all tables or just some? What happens when multiple tables that you need to audit are being updated in a single request? Does the user have to enter their "reason" multiple times?

Are you able to regression test ALL of the triggers? What if there is a bug that sometimes causes and error and the transactions get rollbacked no matter what, or maybe the transactions just get orphaned?

What do you do when the vendor makes changes to the app and/or data model that conflict with what the audit trigger(s)?

What happens if a table is, in the course of an atomic operation from the perspective of the app code, updated multiple times for the same row?

And there are probably some questions I have missed. So, your choices are:

Set up either "Change Data Capture" or "Change Tracking". You can get more info on those from the MSDN page on Tracking Data Changes. This will give you what changed and when, but not who (keeping in mind what Jeff pointed out regarding how many apps are set up to use a standard user for DB interaction which is not the person making the change) or the why. A key selling-point here is that this is completely transparent to the application so you don't need to worry about breaking anything.

Work with the vendor to have them add real auditing to the application. The excuse of "They dont want to work with the vendor of the application for they will charge us" is most likely short-sighted and indicative of bad management. If you spend your time working on this, then they are paying someone to do it. And if they are paying you and not the vendor, then you aren't working on the other (i.e. primary) stuff that they need you to do. And it is fairly likely that the time you do spend on it will be wasted given the low probability of success for a project of this nature (for the reasons stated above) whereas the money spent with the vendor is much less likely to be wasted. In fact, if you somehow mess up the data in trying to inject code in the middle of their operations, then it could be a much higher cost to have you do it as the vendor won't be liable for that loss, plus you are then spending more time in trying to find the problem(s) and possibly recovering from them. Don't get me wrong, it is always possible for this to be cheaper than what the vendor is going to charge and possible to be successful, but the odds are very much not in your favor (hence my first suggestion).

Find another vendor. I know, easier said than done. And that creates problems as well regarding migrating data, time spent on migrating the data, training on the new software, etc. However, it is not impossible so it needed to be mentioned. Even if that new vendor is your in-house IT department. If you need auditing then you need auditing.

EDIT:

Footnote regarding SQL Server never making requests:

It is possible to use SQLCLR to make network requests such as: connections to other DBs, FTP, HTTP & Web Services, etc. While these technically are requests, they generally are (and should be coded to be) non-interactive requests (just like using OPENQUERY / OPENROWSET). There is some inherent risk in making such requests as there can be delays at both the network layer and with the receiver of the requests (i.e. a Web Server that is slow to respond). However, making such requests is usually done in a controlled manner and timeouts can be placed on making the connection and receiving a response.

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR