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, I'm trying to figure out if I can update values in a table based on another value which is in the same table and is updated within a same query. I have 'Num_of_Days' column and 'Due_Date' column. To Calculate Due_Date for Row2, I do this: Row2's Due_Date = Row1's Due_Date + Row1's Num_of_Days. Due_Date is calculated with the previous row's information. I need to create a trigger to update Due_Date at Num_of_Days' update. If Row1's Num_of_Days is updated, then I have to update Row2 and 3's Due_Date based on Row1's new Due_Date. < td> row num Num of Days Due Date 1 1 2013/07 / 10 2 2 2013/07/11 3 null 2013/07/13 变为 1 1 2013/07/10 2 2 2013/07/11 3 null 2013/07/13 becomes row num Num of Days Due Date 1 5 2013/07/10 2 2 2013/07/15 3 null 2013/07/17 由于以下原因，我无法使用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

对不起误解;）



在下面的示例中我使用ç TE [ ^ ]

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