Bud Fox Bud Fox - 3 months ago 7
SQL Question

SQL Server 2008: How to update a field based on another field in the same table

I have a table which contains fields for employees first names and last names. There is also a field for username. How would I make the username automatically populate anytime the first and last name fields are updated?

I want the username field in this format: 'CompanyX\FirstName.LastName'

I have looked into triggers, but I don't think a trigger on a table can update itself. Any help would be appreciated. Thanks!

I am using SQL Server 2008.

Answer

You can make a trigger on the table like this.

/****** Object:  Trigger [dbo].[trg_AuditEmployees]    Script Date: 08/04/2016 10:24:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[trg_AuditEmployees] 
ON [dbo].[employees] 
FOR INSERT, UPDATE
AS 
BEGIN

   update dbo.employees 
   set username = company + 'x\' + lastname + '.' + firstname
   inner join inserted

END