In SQL Server, intercept and change the value a column is set to

Question

I'm trying to improve a legacy database. One problem is that it's missing a lot of foreign key relationships. In some of the columns involved, the associated application is setting fields to an empty string when it should be setting them to null. What I'd like to do is to intercept any attempt to set that column and replace empty strings with NULL before they are applied to the table. The only solution I can think of is to use an INSTEAD OF trigger, but there are some large (in number of column) tables involved and I don't like the maintenance issues this would cause (if someone added a column they'd also have to update the trigger). I'm also concerned about performance implications of this approach.

Am I missing any techniques that might help? The ideal solution would be to fix the legacy application, but it's a huge VB6 app in the (long) process of begin ported to C#, and nobody is willing to devote resources to fixing a problem like that in end of life code.

Thanks, Kev

<====== edit =======>

To be clear, it's not the presence of an empty string in the column that I'm concerned about of itself, it's the fact that that string stops me from creating a foreign key relationship. In general it would not be permissible for me to create dummy rows in the primary key tables to cover this. Though there might be some mileage in renaming the primary key table and creating a view with the old name that filters out these dummy rows. Messy though, so a bit of a last resort.

Answer 1

Difficult one.

If concerned about performance then perhaps run a mysql event at night that change the empty strings to null? That is if it can wait till then. This still means you have to maintain code however. I guess its not impossible to reflect over the schema from a stored proc if you can somehow indicate, by means of convention which fields need to be updated?

Answer 2

If what you're wanting to do is enforce foreign key constraints if the value isn't an empty string (trying to think somewhat laterally here), then you can add a computed column to the table:

ALTER TABLE Tab1 ADD Col1Fixed as CASE WHEN Col1 <> '' THEN Col1 END persisted

And then use that column as the basis of the foreign key constraint, instead of Col1. Then, at least, you get the integrity when the application is writing values that should exist.

Sorry it's not a direct answer to your problem, but as I say in the comment, the only direct way (re-writing column values on the fly) is triggers, to the best of my knowledge.