我们的系统在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
...但是当您需要调用存储过程时,这不起作用.在这些情况下,就我所知,你必须依靠其他方法.
我的问题是,是否有人有关于在触发器中预测数据库操作的问题的最佳/最便宜的方法是什么最好/最便宜的方法是关于修改行中的特定列值是否实际已经改变或者不.上述两种方法都不合理,我想知道是否存在更好的方法.
让我们开始,我永远不会,我的意思是永远不会在触发器中调用存储过程.要考虑多行插入,您必须光标处理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(通过所有字段等)
我想您可能想要使用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中引入的.
虽然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
我建议使用上面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查询的结果放入一个可以在以后引用的表变量中.
我希望这是有趣的:-)
京公网安备 11010802040832号 | 京ICP备19059560号-6 