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

发布于 2024-07-14 17:08:49 字数 1240 浏览 2 评论 0原文

我们的系统运行在 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, '<unique null value>') 
            != ISNULL(i.Col1, '<unique null value>')

需要对您有兴趣测试的每个列重复此代码。然后，您可以检查“更改”值以确定是否执行昂贵的操作。当然，这段代码本身是有问题的，因为它只告诉您该列中至少有一个值在所有已修改的行中发生了更改。

您可以使用以下内容测试各个 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

...但是当您需要调用存储过程时，这效果不佳。在这些情况下，据我所知，您必须求助于其他方法。

我的问题是，是否有人有洞察力（或者，更好的是，硬数据）来了解最好/最便宜的方法是什么，以解决在修改的行中的特定列值是否实际更改或在触发器中预测数据库操作的问题。不是。上述两种方法似乎都不理想，我想知道是否存在更好的方法。

原文

Our system runs on SQL Server 2000, and we are in the process of preparing for an upgrade to SQL Server 2008. We have a lot of trigger code where we need to detect a change in a given column and then operate on that column if it has changed.

Obviously SQL Server provides the UPDATE() and COLUMNS_UPDATED() functions, but these functions only tell you which columns have been implicated in the SQL statement, not which columns have actually changed.

To determine which columns have changed, you need code similar to the following (for a column that supports NULLs):

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, '<unique null value>') 
            != ISNULL(i.Col1, '<unique null value>')

This code needs to be repeated for every column you are interested in testing. You can then check the 'changed' value to determine whether or not to perform expensive operations. Of course, this code is itself problematic, as it only tells you that at least one value in the column has changed over all the rows that were modified.

You can test individual UPDATE statements with something like this:

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

... but this doesn't work well when you are needing to invoke a stored procedure. In those cases you have to fall back on other approaches as far as I can tell.

My question is whether anyone has insight (or, better yet, hard data) as to what the best/cheapest approach is to the problem of predicating a database operation in a trigger on whether a particular column value in a modified row has actually changed or not. Neither of the methods above seem ideal, and I was wondering if a better method exists.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

铁轨上的流浪者 2024-07-21 17:08:49

让我们从我永远不会，我的意思是永远不会在触发器中调用存储过程开始。要考虑多行插入，您必须在过程中移动光标。这意味着您刚刚通过基于集合的查询加载的 200,000 行（例如将所有价格更新 10%）很可能会锁定表几个小时，因为触发器会勇敢地尝试处理负载。另外，如果过程中发生某些变化，您可能会完全中断对表的任何插入，甚至完全挂起表。我坚信触发代码不应调用触发器之外的任何其他内容。

就我个人而言，我更喜欢简单地完成我的任务。如果我在触发器中编写了我想要正确执行的操作，它只会更新、删除或插入已更改的列。

示例：假设由于出于性能原因在两个位置进行了非规范化，因此您想要更新存储在两个位置的 last_name 字段。

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

正如您所看到的，它只会更新与我正在更新的表中当前不同的 lname。

如果您想进行审核并仅记录更改的行，则使用所有字段进行比较，例如
其中 i.field1 <> d.field1 或 i.field2 <> d.field3（等等所有字段）

Let's start with I would never and I mean never invoke a stored proc in a trigger. To account for a multi row insert you would have to cursor through the proc. This means the 200,000 rows you just loaded though a set-based query (say upddating all prices by 10%) might well lock the table for hours as the trigger tries valiantly to handle the load. Plus if something changes in the proc, you could break any inserts to the table at all or even completely hang up the table. I'm a firm beliver that trigger code should call nothing else outside the trigger.

Personally I prefer to simply do my task. If I have written the actions I want to do properly in the trigger it will only update, delete or insert where columns have changed.

Example: suppose you want to update the last_name field that you are storing in two places due to a denormalization placed there for performance reasons.

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

As you can see it would only update the lnames that are different than what is currently in the table I am updating.

If you want to do auditing and record only those rows which changed then do the comparison using all fields something like
where i.field1 <> d.field1 or i.field2 <> d.field3 (etc through all the fields)

回复收藏 0 原文

眼角的笑意。 2024-07-21 17:08:49

我想您可能想使用 EXCEPT 运算符进行调查。它是一个基于集合的运算符，可以清除未更改的行。好处是，当它在 EXCEPT 运算符之前列出的第一个集合中查找行而不是在 EXCEPT 之后列出的第二个集合中查找行时，将 null 值视为相等。

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，以实现基于集合的良好检测更改的方法。我还没有测试过这种方法，但它可能值得您花时间。我认为除了 SQL Server 2005 中引入的。

I think you may want to investigate using the EXCEPT operator. It is a set based operator that can weed out the rows that have not changed. The nice thing is that considers null values as equal as it looks for rows in the first set listed before the EXCEPT operator and not in the second Listed After the 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 */

This handles the issue of columns that allow Nulls without the use of ISNULL() in the trigger and only returns the ids of the rows with changes to col1 for a nice set based approach to detecting changes. I haven't tested the approach but it may well be worth your time. I think EXCEPT was introduced with SQL Server 2005.

回复收藏 0 原文

行至春深 2024-07-21 17:08:49

虽然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

Although HLGEM gave some good advice above, it wasn't exactly what I needed. I've done quite a bit of testing over the past few days, and I figured I'd at least share the results here given that it looks like no more information will be forthcoming.

I set up a table that was effectively a narrower subset (9 columns) of one of our system's primary tables, and populated it with production data so that it was as deep as our production version of the table.

I then duplicated that table, and on the first one wrote a trigger that attempted to detect every individual column change, and then predicated each column update on whether the data in that column had actually changed or not.

For the second table, I wrote a trigger that used extensive conditional CASE logic to do all the updates to all the columns in a single statement.

I then ran 4 tests:

A single-column update to a single row
A single-column update to 10000 rows
A nine-column update to a single row
A nine-column update to 10000 rows

I repeated this test for both indexed and non-indexed versions of the tables, and then repeated the whole thing on SQL 2000 and SQL 2008 servers.

The results I got were fairly interesting:

The second method (one single update statement with hairy CASE logic in the SET clause) was uniformly better-performing than the individual change detection (to a greater or lesser extent depending on the test) with the single exception of a single-column change affecting many rows where the column was indexed, running on SQL 2000. In our particular case we don't do many narrow, deep updates like this, so for my purposes the single-statement approach is definitely the way to go.

I'd be interested in hearing other people's results of similar types of tests, to see whether my conclusions are as universal as I suspect they are or whether they are specific to our particular configuration.

To get you started, here is the test script I used -- you'll obviously need to come up with other data to populate it with:

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

回复收藏 0 原文

余生一个溪 2024-07-21 17:08:49

我建议使用上面 Todd/arghtype 提到的 EXCEPT 集合运算符。

我添加了这个答案，因为我将“插入”放在“删除”之前，以便插入和更新都会被检测到。因此，我通常可以使用一个触发器来涵盖插入和更新。还可以通过添加 OR (NOT EXISTS(SELECT * FROM insert) AND EXISTS(SELECT * FROM returned)) 来检测删除。

它确定值是否仅在指定的列中发生更改。我还没有研究它与其他解决方案的性能比较，但它在我的数据库中运行良好。

它使用 EXCEPT 集合运算符返回左侧查询中未在右侧查询中找到的任何行。此代码可用于 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 查询的结果放入一个表变量中，以便稍后引用。

我希望这引起人们的兴趣:-)

I recommend using the EXCEPT set operator as mentioned by Todd/arghtype above.

I have added this answer because I put the "inserted" before the "deleted" so that INSERTs will be detected as well as UPDATEs. So I can usually have one trigger to cover both inserts and updates. Can also detect deletes by adding OR (NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))

It determines if a value has changed in only the columns specified. I have not investigated its performance compared with the other solutions but it is working well in my database.

It uses the EXCEPT set operator to return any rows from the left query that are not also found on the right query. This code can be used in INSERT, UPDATE and DELETE triggers.

The "PKID" column is the primary key. It is required to enable matching between the two sets. If you have multiple columns for the primary key then you will need to include all the columns to do correct matching between the inserted and deleted sets.

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

If you want to use the changed rows in subsequent trigger logic, I usually put the results of the EXCEPT query into a table variable that can be referenced later on.

I hope this is of interest :-)

回复收藏 0 原文