使用SQL Server 2008 R2中的触发器在更新期间根据另一列的值更改一列的值

时间：2023-02-04 22:55:20

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.

我需要根据同一个表中另一列的值更新记录更新时的列值。更新表的遗留应用程序无法重新编码以处理此问题。

The basic logic would be:

基本逻辑是:

  • If DateShipped is not null, set OrderLocation = 4

    • 如果DateShipped不为null,则设置OrderLocation = 4

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

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

thank you.

1 个解决方案

#1

1  

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

#1

1  

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
标签：

相关文章