SQL Server更新时移动至单独列

Question

我的公司需要在客户表中添加一个列，显示每个客户的先前地址数量。每当地址更新时，此数字必须自动更新。

 CREATE TRIGGER deleted_address ON tblcustomer
 INSTEAD OF INSERT
 AS
 BEGIN
 SET NOCOUNT ON

 end

到目前为止，我已经完成了这个任务，但是我遇到了一个问题，我不知道如何做到当客户的地址被编辑时，将旧地址添加到名为PreviousAddress的新列中，然后更新CustomerAddress。

- JASON

Answer 1

请尝试并查看这是否适用于您：

CREATE TRIGGER deleted_address ON tblcustomer
AFTER UPDATE
AS
BEGIN
    IF UPDATE (CustomerAddress)
    UPDATE t
    SET t.[PreviousAddress] = t.PreviousAddress + 1,
        t.CustomerAddress = i.CustomerAddress
    FROM tblcustomer t
    JOIN INSERTED i ON t.customer_id = i.customer_id
END
GO

- FutbolFan

---

所以，除非“CustomerAddress”列受到影响，否则我的更新将被忽略？ - Andriy M

好的，我已经更新到“after update”了...我之前可能漏掉了那个。谢谢你发现了这个问题！ - FutbolFan

Answer 2

要更新先前的地址，您可以编写如下触发器。

CREATE TRIGGER trg_UpdateAddress
   ON  Customer
   AFTER UPDATE
AS 
BEGIN
    DECLARE @id INT = (SELECT ID FROM DELETED)
    UPDATE dbo.Customer SET PreviousAddress = (SELECT Address FROM DELETED )
    WHERE ID = @id
END

- Nikhil K Mamotty

Answer 3

使用AFTER UPDATE触发器：

CREATE TRIGGER deleted_address ON tblcustomer
AFTER UPDATE
AS
BEGIN
   SET NOCOUNT ON


   IF UPDATE (Address)
      UPDATE t SET countPrev = countPrev + 1, oldAddress = d.Address
      FROM tblcustomer t
      JOIN INSERTED i ON t.customer_id = i.customer_id
      JOIN DELETED d  ON t.customer_id = d.customer_id

END

- Giorgi Nakeuri