MS SQL:更新触发器以根据另一个更新值更新值 [英] MS SQL: Update trigger to update a value based on another updated value
问题描述
我正在试图弄清楚我是否可以根据同一个表中的另一个值更新表中的值并在其中更新一个相同的查询。
我有'Num_of_Days'栏和'Due_Date'栏。要计算Row2的Due_Date,我这样做:Row2的Due_Date = Row1的Due_Date + Row1的Num_of_Days。 Due_Date是根据前一行的信息计算的。
我需要在Num_of_Days更新时创建一个更新Due_Date的触发器。如果Row1的Num_of_Days更新,那么我必须根据Row1的新Due_Date更新Row2和3的Due_Date。
Hi,
变为
becomes
由于以下原因,我无法使用Fetch或loop性能问题,我不在乎。是否可以在单个更新查询中执行此操作?我可以使用临时表等。 提前谢谢。 | |||||||||||||||||||||||||||||||||||||
1 | 5 | 2013/07/10 | |||||||||||||||||||||||||||||||||||
2 | 2 | 2013/07/15 | |||||||||||||||||||||||||||||||||||
3 | null | 2013/07/17 |
I'm not able to use Fetch nor loop due to a performance issue, and I'm out of idea. Is it possible to do this within a single update query? I can use temporary table and such.
Thanks in advance.
推荐答案
看看例子代码:
Have a look at example code:
DECLARE @days TABLE(id INT IDENTITY(1,1), NumOfDays INT NULL, dueDate DATETIME)
INSERT INTO @days (dueDate)
SELECT '2013-06-01'
UNION ALL SELECT '2013-06-07'
UNION ALL SELECT '2013-06-10'
UNION ALL SELECT '2013-06-15'
UNION ALL SELECT '2013-06-19'
UNION ALL SELECT '2013-06-30'
UNION ALL SELECT '2013-07-01'
UNION ALL SELECT '2013-07-05'
UNION ALL SELECT '2013-07-15'
UNION ALL SELECT '2013-07-24'
UPDATE dst
SET NumOfDays = DATEDIFF(dd, dst.dueDate, src.CurrDate)
FROM @days AS dst INNER JOIN (
SELECT id-1 AS id, dueDate AS CurrDate
FROM @days
) AS src ON dst.id = src.id
SELECT id, NumOfDays, CONVERT(NVARCHAR(10),dueDate,121) AS dueDate
FROM @days
Sorry for misunderstanding ;)
In below example i use CTE[^]
DECLARE @days TABLE(id INT IDENTITY(1,1), NumOfDays INT, dueDate DATETIME NULL)
INSERT INTO @days (NumOfDays, dueDate)
SELECT 6, '2013-06-01'
UNION ALL SELECT 3, NULL
UNION ALL SELECT 5, NULL
UNION ALL SELECT 4, NULL
UNION ALL SELECT 11, NULL
UNION ALL SELECT 1, NULL
UNION ALL SELECT 4, NULL
UNION ALL SELECT 10, NULL
UNION ALL SELECT 9, NULL
;WITH dates AS
(
SELECT id, NumOfDays, dueDate, DATEADD(dd,NumOfDays,dueDate) AS newDate
FROM @days
WHERE (dueDate IS NOT NULL) AND (NumOfDays IS NOT NULL)
UNION ALL
SELECT t1.id, t1.NumOfDays, t2.newDate AS dueDate, DATEADD(dd,t1.NumOfDays,t2.newDate) AS newDate
FROM @days AS t1 INNER JOIN dates AS t2 ON t1.id-1 = t2.id
WHERE (t1.dueDate IS NULL) AND (t1.NumOfDays IS NOT NULL)
)
UPDATE dst
SET dueDate = src.dueDate
FROM @days AS dst INNER JOIN dates AS src ON dst.id = src.id
SELECT id, NumOfDays, CONVERT(NVARCHAR(10),dueDate,121) AS dueDate
FROM @days
结果相同;)
Result is the same ;)
1 6 2013-06-01
2 3 2013-06-07
3 5 2013-06-10
4 4 2013-06-15
5 11 2013-06-19
6 1 2013-06-30
7 4 2013-07-01
8 10 2013-07-05
9 9 2013-07-15
更多关于:使用公用表表达式的递归查询 [ ^ ]
[/ EDIT]
More about: Recursive Queries Using Common Table Expressions[^]
[/EDIT]
这篇关于MS SQL:更新触发器以根据另一个更新值更新值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!