SQL Server触发器将NULL更改为空字符串 [英] SQL Server trigger to change NULL to empty strings

问题描述

我正在将Access数据库迁移到SQL Server 2014 Express后端和应用程序，因为它旨在将空字符串放置在某些表的某些列中，而不是NULL(Access在附加表单上的行为). /p>

事实证明，我不能让表不允许这些列中包含null，因为当通过ODBC附加表将其附加到绑定表单时，Access会在有人简单地删除列内容时试图插入NULL值，即使我在表上定义了一个默认值.

我想在前面说我将修复此问题以正确处理NULL，但是对于现在"，我的首要任务是将后端转换为与Access中相同的SQL操作，因此我只是想要一个触发器来将几个字段上的NULL值更改为空字符串，直到这种时候我可以查看所有应用程序逻辑，这些逻辑现在期望这些字段中的空字符串并将其更改为处理NULL.

我想出了以下几点:

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;

这对于单列工作正常.如何最好地对同一表中的多个列执行此操作?我有一个包含4列的表，所有列都可以包含NULL，但是我想用空字符串代替它们.

我应该为每个包含NULL的列还是单独处理IF块吗?当然，如果我立即处理所有这些，我将不得不考虑某些列可能具有合法值，但是如果我做单独的语句，则在插入一列之后它实际上可以运行4次更新.也许这并不重要，因为这都是暂时的，只是对其他更有经验的想法感到好奇.

解决方案

使用以下update语句，一次更新所有四列.此代码未经测试.

      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 

新的触发代码:带有IIF语句

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;

具有ISNULL()函数

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;

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.

解决方案

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;

这篇关于SQL Server触发器将NULL更改为空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！