title: SQL Server trigger to change NULL to empty strings
body: <p>I'm migrating an Access database to a SQL Server 2014 Express back end and the application as it is is designed to put empty strings in some columns in some of the tables instead of NULL's (Access's behavior on attached forms).</p>
  
  <p>It turns out I can't have the table not allow nulls in these columns because when attached to a bound form via ODBC attached tables Access explicitly is trying to insert NULL values when someone simply deletes the contents of a column, even if I have a default value defined on the table.</p>
  
  <p>I want to say up front that I will be fixing this to handle NULLs properly, but for 'right now', my priority is to just get the back end converted to SQL operating the same as it was in Access, so I just want a trigger to change NULL values on a few fields to empty strings until such a time when I can look at all the application logic which is expecting empty strings in these fields right now and change it to handle NULLs.</p>
  
  <p>I came up with the following:</p>
  
  <pre><code>CREATE TRIGGER TABLE1_ReplaceNulls 
  ON TABLE1
  AFTER INSERT, UPDATE
  AS
      IF UPDATE(FIELDWNULL1) 
      BEGIN
          UPDATE TABLE1 
          SET FIELDWNULL1 = '' 
          FROM inserted I 
          INNER JOIN TABLE1 ON I.PKFIELD = TABLE1.PKFIELD 
          WHERE I.FIELDWNULL1 IS NULL;
      END;
  </code></pre>
  
  <p>This works fine for a single column. How best do I do this for multiple columns in the same table? I have one table with 4 columns that all could contain NULLS, but I want empty strings in place of them.  </p>
  
  <p>Should I do a separate IF block for each column that could contain the NULL or just handle it all at once?  Of course, if I handle it all at once I would have to consider some of the columns might have legit values, but if I do separate statements then it could essentially run 4 updates after a column is inserted. Maybe it doesn't matter as this is all temporary, but just curious on other more experienced thoughts.</p>