问题描述
我有一个包含两列的表格，我需要其中一列 (columnB) 作为另一列 (columnA) 的副本.因此，如果插入或更新一行，我希望将 columnA 中的值复制到 columnB.
I have a table with two columns where I need one (columnB) to be a copy of the other one (columnA). So, if a row is inserted or updated, I want the value from columnA to be copied to columnB.
这是我现在所拥有的:
CREATE TRIGGER tUpdateColB
ON products
FOR INSERT, UPDATE AS
BEGIN
UPDATE table
SET columnB = columnA
END
现在的问题是查询会影响所有行，而不仅仅是更新或插入的行.我将如何解决这个问题?
The problem now is that the query affects all rows, not just the one that was updated or inserted. How would I go about fixing that?
推荐答案
假设你有一个主键列，id，(你应该有一个主键)，加入插入表(使触发器能够处理多行):
Assuming you have a primary key column, id, (and you should have a primary key), join to the inserted table (making the trigger capable of handling multiple rows):
CREATE TRIGGER tUpdateColB
ON products
FOR INSERT, UPDATE AS
BEGIN
UPDATE table
SET t.columnB = i.columnA
FROM table t INNER JOIN inserted i ON t.id = i.id
END
但如果 ColumnB 始终是 ColumnA 的副本，为什么不创建一个 计算列 代替?
But if ColumnB is always a copy of ColumnA, why not create a Computed column instead?
使用插入和删除的表
这篇关于如何制作仅影响已更新/插入的行的触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！