CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Bud Fox
Bud Fox

Reputation: 37

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.

Upvotes: 1

Views: 903

Answers (3)

Seth Winters
Seth Winters

Reputation: 76

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
   where id = select id from inserted

END

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

Create computed column username as concatenating of all these attributes

Upvotes: 0

SS_DBA
SS_DBA

Reputation: 2423

Here is an example of a computed column. I tested it and it worked fine.

CREATE TABLE [dbo].[Table_1](
    [Company] [varchar](50) NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [UserName]  AS (((([Company]+'\')+[FirstName])+'.')+[LastName]) PERSISTED
) ON [PRIMARY]

GO

Upvotes: 4

Related Questions