Clever Geek Handbook

SQL Server moves to separate column on upgrade

My company requires a column in the table Customer

that will show the number of previous addresses for any customer. This number should be updated automatically when the address is updated.

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

 end

So far I have it, but I came to a question where I don’t know how to do this, if the client's address is edited, it adds the old address to a new column named PreviousAddress

and then updatesCustomerAddress

+3


source to share


3 answers


Try it and see if this works for you:



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
0


source


To update the previous address, you can write a trigger like this.



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
0


source


Use trigger 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
0


source






More articles:


All Articles