使用 SQL Server 2008 R2 中的触发器在更新期间根据另一列的值更改一列的值 [英] Use a trigger in SQL Server 2008 R2 to change the value of one column based on the value of another column during update

问题描述

我需要在更新记录时根据同一表中另一列的值更改列的值.无法重新编码更新表的旧应用程序来处理此问题.

I need to change the value of a column when a record is updated, based on the value of another column in the same table. The legacy application updating the table cannot be re-coded to handle this.

基本逻辑是:

• 如果DateShipped 不为空，设置OrderLocation = 4

• If DateShipped is not null, set OrderLocation = 4

希望我可以使用 SQL Server 2008 R2 中的更新触发器在数据库级别执行此操作.

Hoping I can do this at the database level with an update trigger in SQL Server 2008 R2.

谢谢.

推荐答案

CREATE TRIGGER tr_OrderLocation_Update
ON TableName 
FOR UPDATE,INSERT
BEGIN
  SET NOCOUNT ON;

  UPDATE t
   SET t.OrderLocation = 4
  FROM TableName t INNER JOIN inserted i 
  ON t.PK_Column = i.PK_Column  --<-- what ever the primary key column
  WHERE i.DateShipped IS NOT NULL
END

这篇关于使用 SQL Server 2008 R2 中的触发器在更新期间根据另一列的值更改一列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！