SQL Server trigger to change NULL to empty strings

Question

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).

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.

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.

I came up with the following:

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;

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.

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.

Access ONLY puts empty strings if designed that way. As general rule adopting a standard throughout the application to store nulls is preferred. If the Access table design had followed this standard, then the migration to SQL server would be seamless. If Access table design did not allow ZLS, then blanking out the text box in an Access form will result in a null value in the table. Migration of such applications to SQL server will thus continue to work without changes to the VBA code. Blanking out a text box will save null in BOTH SQL server and Access. One should avoid a mix of ZLS and nulls — Albert D. Kallal
– Albert D. Kallal, Commented Jan 11, 2016 at 22:28

Hiten004 · Accepted Answer · 2016-01-11 16:44:41Z

1

Using below update statement,update all four column in one trans. This code is not tested.

      UPDATE TABLE1 
        SET FIELDWNULL1=iif(FIELDWNULL1 is null,'',FIELDWNULL1),
        FIELDWNULL2=iif(FIELDWNULL2 is null,'',FIELDWNULL2), 
        FIELDWNULL3=iif(FIELDWNULL3 is null,'',FIELDWNULL3), 
        FIELDWNULL4=iif(FIELDWNULL4 is null,'',FIELDWNULL4) 

        FROM inserted I INNER JOIN TABLE1
            ON I.PKFIELD = TABLE1.PKFIELD

New trigger code: With IIF statement

CREATE TRIGGER TABLE1_ReplaceNulls ON TABLE1
AFTER INSERT, UPDATE
AS

--IF UPDATE(FIELDWNULL1) 
    BEGIN
        UPDATE TABLE1 
        SET FIELDWNULL1=iif(FIELDWNULL1 is null,'',FIELDWNULL1),
        FIELDWNULL2=iif(FIELDWNULL2 is null,'',FIELDWNULL2), 
        FIELDWNULL3=iif(FIELDWNULL3 is null,'',FIELDWNULL3), 
        FIELDWNULL4=iif(FIELDWNULL4 is null,'',FIELDWNULL4) 

        FROM inserted I INNER JOIN TABLE1
            ON I.PKFIELD = TABLE1.PKFIELD 
            --WHERE I.FIELDWNULL1 IS NULL;
    END;

With ISNULL() function

CREATE TRIGGER TABLE1_ReplaceNulls ON TABLE1
AFTER INSERT, UPDATE
AS

--IF UPDATE(FIELDWNULL1) 
    BEGIN
        UPDATE TABLE1 
        SET FIELDWNULL1=ISNULL(FIELDWNULL1,''),
        FIELDWNULL2=ISNULL(FIELDWNULL2,''), 
        FIELDWNULL3=ISNULL(FIELDWNULL3,''), 
        FIELDWNULL4=ISNULL(FIELDWNULL4,'') 

        FROM inserted I INNER JOIN TABLE1
            ON I.PKFIELD = TABLE1.PKFIELD 
            --WHERE I.FIELDWNULL1 IS NULL;
    END;

edited Jan 11, 2016 at 16:44

answered Jan 8, 2016 at 19:58

Hiten004

2,5011 gold badge24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Travis P Over a year ago

ok, that's what I was thinking along option 2. I thought checking to make sure fields were updated might be slightly more efficient, but as I said, i guess efficiency isn't really a concern right now since this should all be temporary anyway. I will test this out shortly to verify it works as intended. Thanks.

Hiten004 Over a year ago

@TravisP Please let stackoverflow know if it work or not !!

Gord Thompson Over a year ago

(cc: @TravisP ) - It would be a bit more compact to use ISNULL(FieldName, '') but otherwise it looks like a good workaround.

Collectives™ on Stack Overflow

SQL Server trigger to change NULL to empty strings

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related