Not even sure if this is possible???

Question

Not even sure if this is possible???

brisims34

SSC-Addicted

Points: 479
More actions
June 27, 2014 at 9:07 pm

#396975

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!
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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

If you make the column in the table NOT NULL and make the field in the app a required field, I think that would be a much better solution than a trigger even if it were and Instead of Trigger that caught the error before the actual insert to the table so there'd be no rollback. In this case, the Trigger would be classified as spaghetti code, IMHO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

brisims34 SSC-Addicted Points: 479 More actions · Answer 2

Thanks for the quick response. I fully agree with that approach however, I cannot alter the application... I could make the field required but there is no place for the user to input. here is the basic scenario:

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

I feel your pain and your boss' pain. Dealing with 3rd party application vendors is a huge pain especially when it seems that they break more stuff than they fix when they make a change.

There's a fly in the ointment that's the size of Alaska with this request. Most applications aren't setup to use the credentials of the person that logged into the application. Rather, the application is given a login and that's the login that SQL Server (or any intercept application you could write) will see. Unless there's something in the data being passed to SQL Server by the application (perhaps a "ModifiedBy" field), then using even the ORIGINAL_LOGIN() function in T-SQL will only show that the application made the change. Even then, some ORMs will mess with multiple changes in that some ORMs won't send data that hasn't changed. That would affect things like a "ModifiedBY" field in that if the same person makes multiple changes, the "ModifiedBy" field won't contain a value or may be missing entirely.

IF you can work around those problems, then here's what I would do.

Write and "Instead Of" trigger on the table. It's a bit more complicated than a regular "After" trigger but it gives you a huge amount of control over what is allowed to affect the underlying table and it can do so without a ROLLBACK, which is quite expensive.

This trigger would be setup to examine the conditions of the change-rows. If everything with the new "record" is hunky-dory, then write it to the final table. If the necessary columns aren't filled in properly, write that data to a permanent staging table (instead of the final table) with a DATETIME, a flag of some sort to identify it as a new "record", and a column that identifies what's wrong with the new "record". This would keep the transaction from holding everyone else up as the trigger would allow the transaction to complete immediately. The trigger could return an informational "error" to the app using RAISERROR but you have to be a little careful there. The use of RAISERROR could cause a ROLLBACK and that could rollback the row the trigger just saved to the staging table.

Not being allowed to change the application means that the informational "error" message might not actually show up on the user screen. That leaves you with writing a job that regularly scans the staging table for new rows and sending an email to the person responsible for the row. Of course, that will also require some human handling/entry of data to correct the data on the row that's been sequestered in the staging table. Again, if the row can't be identified as to "who-dun-it", the process is pretty much dead in the water.

There are a lot of caveats to any of the above, the worst, of course, being the fact that the app probably passes no "who-dun-it" information and probably doesn't reflect the original login because it uses its own login. Unless the app consistently passes user-identifying information with every change-row the, understanding all of the pain and expense in doing so, my recommendation would be to have someone fix the app.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 4

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: