17赞

检测MS SQL Server中列更改的最有效方法

作者：个性2402852463 | 2023-08-29 12:35

如何解决《检测MSSQLServer中列更改的最有效方法》经验，为你挑选了4个好方法。

我们的系统在SQL Server 2000上运行,我们正在准备升级到SQL Server 2008.我们有很多触发器代码,我们需要检测给定列中的更改,然后对该列进行操作已经改变.

显然,SQL Server提供了UPDATE()和COLUMNS_UPDATED()函数,但这些函数只告诉您SQL语句中涉及哪些列,而不是实际更改了哪些列.

要确定哪些列已更改,您需要类似于以下代码(对于支持NULL的列):

IF UPDATE(Col1)
    SELECT @col1_changed = COUNT(*) 
    FROM Inserted i
        INNER JOIN Deleted d ON i.Table_ID = d.Table_ID
    WHERE ISNULL(i.Col1, '') 
            != ISNULL(i.Col1, '')

对于您有兴趣测试的每个列,都需要重复此代码.然后,您可以检查"已更改"值以确定是否执行昂贵的操作.当然,这段代码本身也存在问题,因为它只告诉您列中的至少一个值已经修改了所有行.

您可以使用以下内容测试单个UPDATE语句:

UPDATE Table SET Col1 = CASE WHEN i.Col1 = d.Col1 
          THEN Col1 
          ELSE dbo.fnTransform(Col1) END
FROM Inserted i
    INNER JOIN Deleted d ON i.Table_ID = d.Table_ID

...但是当您需要调用存储过程时,这不起作用.在这些情况下,就我所知,你必须依靠其他方法.

我的问题是,是否有人有关于在触发器中预测数据库操作的问题的最佳/最便宜的方法是什么最好/最便宜的方法是关于修改行中的特定列值是否实际已经改变或者不.上述两种方法都不合理,我想知道是否存在更好的方法.

1> HLGEM..：

让我们开始,我永远不会,我的意思是永远不会在触发器中调用存储过程.要考虑多行插入,您必须光标处理proc.这意味着通过基于集合的查询刚刚加载的200,000行(比如将所有价格上调10%)可能会在触发器尝试处理负载时尝试将表锁定几个小时.此外,如果proc中的某些内容发生了变化,您可以完全破坏表中的任何插入,甚至可以完全挂起表.我是一个坚定的因素,触发代码应该在触发器之外调用其他任何东西.

就个人而言,我更愿意完成我的任务.如果我在触发器中编写了我想要正确执行的操作,则只会更新,删除或插入列已更改的位置.

示例:假设您要更新存储在两个位置的last_name字段,原因是出于性能原因而放置了非规范化.

update t
set lname = i.lname
from table2 t 
join inserted i on t.fkfield = i.pkfield
where t.lname <>i.lname

正如您所看到的,它只会更新与我正在更新的表中当前不同的名称.

如果你想进行审计并仅记录那些已更改的行,那么使用所有字段进行比较,例如i.field1 <> d.field1或i.field2 <> d.field3(通过所有字段等)

2> 小智..：

我想您可能想要使用EXCEPT运算符进行调查.它是一个基于集合的运算符,可以清除未更改的行.好处是认为空值等于它在EXCEPT运算符之前列出的第一个集合中的行,而不是在EXCEPT之后的第二个列表中查找

WITH ChangedData AS (
SELECT d.Table_ID , d.Col1 FROM deleted d
EXCEPT 
SELECT i.Table_ID , i.Col1  FROM inserted i
)
/*Do Something with the ChangedData */

这样可以处理允许Null而不ISNULL()在触发器中使用的列的问题,并且只返回对col1进行更改的行的id,以便基于良好的基于集合的方法来检测更改.我没有测试过这种方法,但它可能值得你花时间.我认为EXCEPT是在SQL Server 2005中引入的.

3> mwigdahl..：

虽然HLGEM上面给出了一些好的建议,但这并不是我所需要的.我在过去的几天里做了很多测试,我认为我至少可以在这里分享结果,因为看起来似乎没有更多的信息可以提供.

我建立了一个表,它实际上是我们系统主表之一的一个较窄的子集(9列),并用生产数据填充它,使其与表的生产版本一样深.

然后我复制了该表,并在第一个表上写了一个触发器,试图检测每个列的更改,然后根据该列中的数据是否实际更改来预测每个列的更新.

对于第二个表,我编写了一个触发器,它使用广泛的条件CASE逻辑对单个语句中的所有列进行所有更新.

然后我跑了4个测试:

单列更新到单行

单列更新为10000行

对单行进行九列更新

九列更新到10000行

我为表的索引和非索引版本重复了这个测试,然后在SQL 2000和SQL 2008服务器上重复了整个过程.

我得到的结果相当有趣:

第二种方法(在SET子句中使用毛茸茸的CASE逻辑的单个更新语句)统一性能比单个更改检测更好(在较大或较小程度上取决于测试),但影响单列更改的唯一例外列被索引的许多行,在SQL 2000上运行.在我们的特定情况下,我们不做这样的许多窄,深度更新,所以对于我的目的,单语句方法绝对是要走的路.

我有兴趣听听其他人类似测试的结果,看看我的结论是否像我怀疑的那样普遍,或者它们是否特定于我们的特定配置.

为了帮助您入门,这是我使用的测试脚本 - 您显然需要提供其他数据来填充它:

create table test1
( 
    t_id int NOT NULL PRIMARY KEY,
    i1 int NULL,
    i2 int NULL,
    i3 int NULL,
    v1 varchar(500) NULL,
    v2 varchar(500) NULL,
    v3 varchar(500) NULL,
    d1 datetime NULL,
    d2 datetime NULL,
    d3 datetime NULL
)

create table test2
( 
    t_id int NOT NULL PRIMARY KEY,
    i1 int NULL,
    i2 int NULL,
    i3 int NULL,
    v1 varchar(500) NULL,
    v2 varchar(500) NULL,
    v3 varchar(500) NULL,
    d1 datetime NULL,
    d2 datetime NULL,
    d3 datetime NULL
)

-- optional indexing here, test with it on and off...
CREATE INDEX [IX_test1_i1] ON [dbo].[test1] ([i1])
CREATE INDEX [IX_test1_i2] ON [dbo].[test1] ([i2])
CREATE INDEX [IX_test1_i3] ON [dbo].[test1] ([i3])
CREATE INDEX [IX_test1_v1] ON [dbo].[test1] ([v1])
CREATE INDEX [IX_test1_v2] ON [dbo].[test1] ([v2])
CREATE INDEX [IX_test1_v3] ON [dbo].[test1] ([v3])
CREATE INDEX [IX_test1_d1] ON [dbo].[test1] ([d1])
CREATE INDEX [IX_test1_d2] ON [dbo].[test1] ([d2])
CREATE INDEX [IX_test1_d3] ON [dbo].[test1] ([d3])

CREATE INDEX [IX_test2_i1] ON [dbo].[test2] ([i1])
CREATE INDEX [IX_test2_i2] ON [dbo].[test2] ([i2])
CREATE INDEX [IX_test2_i3] ON [dbo].[test2] ([i3])
CREATE INDEX [IX_test2_v1] ON [dbo].[test2] ([v1])
CREATE INDEX [IX_test2_v2] ON [dbo].[test2] ([v2])
CREATE INDEX [IX_test2_v3] ON [dbo].[test2] ([v3])
CREATE INDEX [IX_test2_d1] ON [dbo].[test2] ([d1])
CREATE INDEX [IX_test2_d2] ON [dbo].[test2] ([d2])
CREATE INDEX [IX_test2_d3] ON [dbo].[test2] ([d3])

insert into test1 (t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3)
-- add data population here...

insert into test2 (t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3)
select t_id, i1, i2, i3, v1, v2, v3, d1, d2, d3 from test1

go

create trigger test1_update on test1 for update
as
begin

declare @i1_changed int,
    @i2_changed int,
    @i3_changed int,
    @v1_changed int,
    @v2_changed int,
    @v3_changed int,
    @d1_changed int,
    @d2_changed int,
    @d3_changed int

IF UPDATE(i1)
    SELECT @i1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.i1,0) != ISNULL(d.i1,0)
IF UPDATE(i2)
    SELECT @i2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.i2,0) != ISNULL(d.i2,0)
IF UPDATE(i3)
    SELECT @i3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.i3,0) != ISNULL(d.i3,0)
IF UPDATE(v1)
    SELECT @v1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.v1,'') != ISNULL(d.v1,'')
IF UPDATE(v2)
    SELECT @v2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.v2,'') != ISNULL(d.v2,'')
IF UPDATE(v3)
    SELECT @v3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.v3,'') != ISNULL(d.v3,'')
IF UPDATE(d1)
    SELECT @d1_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.d1,'1/1/1980') != ISNULL(d.d1,'1/1/1980')
IF UPDATE(d2)
    SELECT @d2_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.d2,'1/1/1980') != ISNULL(d.d2,'1/1/1980')
IF UPDATE(d3)
    SELECT @d3_changed = COUNT(*) FROM Inserted i INNER JOIN Deleted d
        ON i.t_id = d.t_id WHERE ISNULL(i.d3,'1/1/1980') != ISNULL(d.d3,'1/1/1980')

if (@i1_changed > 0)
begin
    UPDATE test1 SET i1 = CASE WHEN i.i1 > d.i1 THEN i.i1 ELSE d.i1 END
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.i1 != d.i1
end

if (@i2_changed > 0)
begin
    UPDATE test1 SET i2 = CASE WHEN i.i2 > d.i2 THEN POWER(i.i2, 1.1) ELSE POWER(d.i2, 1.1) END
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.i2 != d.i2
end

if (@i3_changed > 0)
begin
    UPDATE test1 SET i3 = i.i3 ^ d.i3
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.i3 != d.i3
end

if (@v1_changed > 0)
begin
    UPDATE test1 SET v1 = i.v1 + 'a'
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.v1 != d.v1
end

UPDATE test1 SET v2 = LEFT(i.v2, 5) + '|' + RIGHT(d.v2, 5)
FROM test1
    INNER JOIN inserted i ON test1.t_id = i.t_id
    INNER JOIN deleted d ON i.t_id = d.t_id

if (@v3_changed > 0)
begin
    UPDATE test1 SET v3 = LEFT(i.v3, 5) + '|' + LEFT(i.v2, 5) + '|' + LEFT(i.v1, 5)
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.v3 != d.v3
end

if (@d1_changed > 0)
begin
    UPDATE test1 SET d1 = DATEADD(dd, 1, i.d1)
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.d1 != d.d1
end

if (@d2_changed > 0)
begin
    UPDATE test1 SET d2 = DATEADD(dd, DATEDIFF(dd, i.d2, d.d2), d.d2)
    FROM test1
        INNER JOIN inserted i ON test1.t_id = i.t_id
        INNER JOIN deleted d ON i.t_id = d.t_id
    WHERE i.d2 != d.d2
end

UPDATE test1 SET d3 = DATEADD(dd, 15, i.d3)
FROM test1
    INNER JOIN inserted i ON test1.t_id = i.t_id
    INNER JOIN deleted d ON i.t_id = d.t_id

end

go

create trigger test2_update on test2 for update
as
begin

    UPDATE test2 SET
        i1 = 
            CASE
            WHEN ISNULL(i.i1, 0) != ISNULL(d.i1, 0)
            THEN CASE WHEN i.i1 > d.i1 THEN i.i1 ELSE d.i1 END
            ELSE test2.i1 END,
        i2 = 
            CASE
            WHEN ISNULL(i.i2, 0) != ISNULL(d.i2, 0)
            THEN CASE WHEN i.i2 > d.i2 THEN POWER(i.i2, 1.1) ELSE POWER(d.i2, 1.1) END
            ELSE test2.i2 END,
        i3 = 
            CASE
            WHEN ISNULL(i.i3, 0) != ISNULL(d.i3, 0)
            THEN i.i3 ^ d.i3
            ELSE test2.i3 END,
        v1 = 
            CASE
            WHEN ISNULL(i.v1, '') != ISNULL(d.v1, '')
            THEN i.v1 + 'a'
            ELSE test2.v1 END,
        v2 = LEFT(i.v2, 5) + '|' + RIGHT(d.v2, 5),
        v3 = 
            CASE
            WHEN ISNULL(i.v3, '') != ISNULL(d.v3, '')
            THEN LEFT(i.v3, 5) + '|' + LEFT(i.v2, 5) + '|' + LEFT(i.v1, 5)
            ELSE test2.v3 END,
        d1 = 
            CASE
            WHEN ISNULL(i.d1, '1/1/1980') != ISNULL(d.d1, '1/1/1980')
            THEN DATEADD(dd, 1, i.d1)
            ELSE test2.d1 END,
        d2 = 
            CASE
            WHEN ISNULL(i.d2, '1/1/1980') != ISNULL(d.d2, '1/1/1980')
            THEN DATEADD(dd, DATEDIFF(dd, i.d2, d.d2), d.d2)
            ELSE test2.d2 END,
        d3 = DATEADD(dd, 15, i.d3)
    FROM test2
        INNER JOIN inserted i ON test2.t_id = i.t_id
        INNER JOIN deleted d ON test2.t_id = d.t_id

end

go

-----
-- the below code can be used to confirm that the triggers operated identically over both tables after a test
select top 10 test1.i1, test2.i1, test1.i2, test2.i2, test1.i3, test2.i3, test1.v1, test2.v1, test1.v2, test2.v2, test1.v3, test2.v3, test1.d1, test1.d1, test1.d2, test2.d2, test1.d3, test2.d3
from test1 inner join test2 on test1.t_id = test2.t_id
where 
    test1.i1 != test2.i1 or 
    test1.i2 != test2.i2 or
    test1.i3 != test2.i3 or
    test1.v1 != test2.v1 or 
    test1.v2 != test2.v2 or
    test1.v3 != test2.v3 or
    test1.d1 != test2.d1 or 
    test1.d2 != test2.d2 or
    test1.d3 != test2.d3

-- test 1 -- one column, one row
update test1 set i3 = 64 where t_id = 1000
go
update test2 set i3 = 64 where t_id = 1000
go

update test1 set i3 = 64 where t_id = 1001
go
update test2 set i3 = 64 where t_id = 1001
go

-- test 2 -- one column, 10000 rows
update test1 set v3 = LEFT(v3, 50) where t_id between 10000 and 20000
go
update test2 set v3 = LEFT(v3, 50) where t_id between 10000 and 20000
go

-- test 3 -- all columns, 1 row, non-self-referential
update test1 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id = 3000
go
update test2 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id = 3000
go

-- test 4 -- all columns, 10000 rows, non-self-referential
update test1 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id between 30000 and 40000
go
update test2 set i1 = 1000, i2 = 2000, i3 = 3000, v1 = 'R12345123', v2 = 'Happy!', v3 = 'I am v3!!!', d1 = '1/1/1985', d2 = '1/1/1988', d3 = NULL
where t_id between 30000 and 40000
go

-----

drop table test1
drop table test2

4> David Coster..：

我建议使用上面Todd/arghtype提到的EXCEPT set运算符.

我已添加此答案,因为我在"已删除"之前放置了"插入",以便检测INSERT以及UPDATE.所以我通常可以有一个触发器来覆盖插入和更新.也可以通过添加OR(NOT EXISTS(SELECT*FROM inserted)和EXISTS(SELECT*FROM deleted)来检测删除

它确定值是否仅在指定的列中更改.与其他解决方案相比,我没有调查过它的性能,但它在我的数据库中运行良好.

它使用EXCEPT set运算符返回左查询中的任何行,这些行在右侧查询中也找不到.此代码可用于INSERT,UPDATE和DELETE触发器.

"PKID"列是主键.需要在两组之间启用匹配.如果主键有多列,则需要包含所有列以在插入和删除的集之间进行正确匹配.

-- Only do trigger logic if specific field values change.
IF EXISTS(SELECT  PKID
                ,Column1
                ,Column7
                ,Column10
          FROM inserted
          EXCEPT
          SELECT PKID
                ,Column1
                ,Column7
                ,Column10
          FROM deleted )    -- Tests for modifications to fields that we are interested in
OR (NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)) -- Have a deletion
BEGIN
          -- Put code here that does the work in the trigger

END

如果要在后续触发器逻辑中使用更改的行,我通常会将EXCEPT查询的结果放入一个可以在以后引用的表变量中.

我希望这是有趣的:-)

推荐阅读

程序员
SIMD/SSE:如何检查所有向量元素是否为非零

如何解决《SIMD/SSE:如何检查所有向量元素是否为非零》经验，为你挑选了1个好方法。 ... [详细]
程序员
使用带有scikit-learn的Lasso进行特征选择

如何解决《使用带有scikit-learn的Lasso进行特征选择》经验，为你挑选了0个好方法。 ... [详细]
程序员
Redis - 手动提升从属设备

如何解决《Redis-手动提升从属设备》经验，为你挑选了1个好方法。 ... [详细]
程序员
从图库中获取图像以在片段中的imageview中设置？

如何解决《从图库中获取图像以在片段中的imageview中设置？》经验，为你挑选了0个好方法。 ... [详细]
程序员
python,matplotlib:specgram数据数组值与specgram plot不匹配

如何解决《python,matplotlib:specgram数据数组值与specgramplot不匹配》经验，为你挑选了1个好方法。 ... [详细]
程序员
std :: string没有将数据存储在连续的内存位置的好处(在C++ 11之前)

如何解决《std::string没有将数据存储在连续的内存位置的好处(在C++11之前)》经验，为你挑选了1个好方法。 ... [详细]
程序员
为什么java.util.logging.Logger打印到stderr？

如何解决《为什么java.util.logging.Logger打印到stderr？》经验，为你挑选了0个好方法。 ... [详细]
程序员
MockMvc和Spring Security - Null FilterChainProxy

如何解决《MockMvc和SpringSecurity-NullFilterChainProxy》经验，为你挑选了0个好方法。 ... [详细]
程序员
子进程cp返回错误 - bufsize必须是整数

如何解决《子进程cp返回错误-bufsize必须是整数》经验，为你挑选了1个好方法。 ... [详细]
程序员
如何在Heroku Local上查看未修剪的日志

如何解决《如何在HerokuLocal上查看未修剪的日志》经验，为你挑选了1个好方法。 ... [详细]
程序员
在Opencv和python中的K-means聚类:在mahalanobis距离中是否有任何聚类选项？

如何解决《在Opencv和python中的K-means聚类:在mahalanobis距离中是否有任何聚类选项？》经验，为你挑选了1个好方法。 ... [详细]
程序员
Haskell - 接受不同的类型并采取相应的行动

如何解决《Haskell-接受不同的类型并采取相应的行动》经验，为你挑选了2个好方法。 ... [详细]
程序员
在Crystal Report中的每条记录后插入空白页

如何解决《在CrystalReport中的每条记录后插入空白页》经验，为你挑选了0个好方法。 ... [详细]
程序员
Java 8:使用流转换日期

如何解决《Java8:使用流转换日期》经验，为你挑选了1个好方法。 ... [详细]
程序员
如何将JSON保存到本地文本文件

如何解决《如何将JSON保存到本地文本文件》经验，为你挑选了2个好方法。 ... [详细]
程序员
错误:此标识池不支持指定的开发人员提供程序

如何解决《错误:此标识池不支持指定的开发人员提供程序》经验，为你挑选了1个好方法。 ... [详细]
程序员
SELECT + SUM + LEFT JOIN

如何解决《SELECT+SUM+LEFTJOIN》经验，为你挑选了1个好方法。 ... [详细]
程序员
为什么Java允许使用Interface构建obj

如何解决《为什么Java允许使用Interface构建obj》经验，为你挑选了0个好方法。 ... [详细]
程序员
RabbitMqBundle使用者退出,异常"读取数据时出错.收到0而不是预期的1个字节"和"管道断开或关闭"

如何解决《RabbitMqBundle使用者退出,异常"读取数据时出错.收到0而不是预期的1个字节"和"管道断开或关闭"》经验，为你挑选了0个好方法。 ... [详细]
程序员
Java中的一个神秘字符串

如何解决《Java中的一个神秘字符串》经验，为你挑选了1个好方法。 ... [详细]

个性2402852463

这个屌丝很懒，什么也没留下！

关注作者

Tags | 热门标签

RankList | 热门文章