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
JASON
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
Futbolfan
source
to share
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
Nikhil K Mamotty
source
to share
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
Giorgi nakeuri
source
to share