SQL Server 而不是插入触发器 - 有没有一种简单的方法来修改单个列?
鉴于SQL Server不允许在触发器中修改逻辑INSERTED和DELETED表,是否有一种简单的方法可以更改单个列的值而不必重新编写整个插入语句?
例如,我应用触发器的表有 20 列。我想修改 INSERTED 表中一列(每行)的值,然后将该行插入表中。我可以在不编写包含 19 列加上我要修改其值的一列的插入语句的情况下执行此操作吗?
Given that SQL Server does not allow modification of the logical INSERTED and DELETED tables in a trigger, is there an easy way to change the value of a single column and not have to re-write the entire insert statement?
For example, the table to which I am applying the trigger has 20 columns. I want to modify the value from the INSERTED table for one column (per row) and then insert that row into the table. Can I do so without writing an insert statement with 19 columns plus the one column whose value I'm modifying?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
是的,只需使用“插入后”触发器,而不是“代替插入”触发器。
这样,该行就已经被插入到表中,您所要做的就是发出一个(精心设计的)更新语句,通常是通过内连接到主键字段上的 INSERTED 表。
Yes, just use an "after insert" trigger, instead of a "instead-of insert" trigger.
That way the row has already been inserted into the table, and all you have to do is issue a (carefully crafted) update statement, usually by inner-joining to the INSERTED table on the primary key field.
要向 BradC 的答案添加更多详细信息:
带有连接的更新的语法有点不稳定
To add more detail to BradCs answer:
The syntax for an update with a join is a little wonky