AKADER AKADER - 3 months ago 12
SQL Question

SQL Server : if statement to check if columns have been modified

I have a table with 3 columns

FirstName
,
LastName
, and
FullName
(don't ask why). I would like to create a SQL Server trigger to update
FullName
when one of the two columns has been modified. How can I check if the columns were updated? Or is there a better alternative to this problem besides ripping it out? It is being used in many places throughout the app

CREATE TRIGGER tr_UPDATE_FULLNAME
ON [dbo].[Person]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

IF UPDATE (FirstName) // not sure if this is correct
BEGIN
declare @fName nvarchar(50), @lName nvarchar(50)
select @fName = FirstName from inserted
select @lName = LastName from inserted

UPDATE [dbo].[Person]

SET FullName = @fName + ' ' + @lName
WHERE FirstName = @fName and LastName = @lName
END
END

Answer

You don't need triggers at all. Computed column suits very good your purposes.

Because FullName column is in the same table it will be more simple to use computed column.

ALTER TABLE [dbo].[Person] 
    ADD FullName AS (COALESCE(@FirstName + ' ' + @LastName, @FirstName , @LastName, ''));

Computed Columns

If FullName column already created you need to drop it before changing to computed column.

ALTER TABLE [dbo].[Person] DROP COLUMN FullName;
Comments