Click here to Skip to main content
15,072,776 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

I have a table (Customer) in SQL Server 2008. There are 4 columns in this table "ID", "FirstName", "LastName", "Code".

Now I want that whenever someone insert data in columns (ID, FirstName and LastName), the "Code" column should be automatically filled with a value.

The value in "Code" column will be => "ID" + First letter of "FirstName" + First letter of "LastName".

For example - If I insert "101" in ID field, "John" in FirstName field and "Lambart" in LastName field then the "Code" column should have a value "101JL".

I want to do it by trigger.

Please help. Thanks in advanced :)

Might be I am deviating you from the issue. You can think of having computed column with persistence.[^]
In this case you don't need a trigger - you can just have a computed column - much easier and less expensive

Computed columns[^]

Add the column to the table, then in the Computed Column specification add the following.

Convert(varchar(10), [ID]) + Left([FirstName],1) + Left([LastName],1)

The column is effectively display only - you cannot insert to or update the colun, but changes to any of the referenced columns will automatically adjust the computed vale
Here you can try following demo to perform your required operation using trigger only or otherwise you can use computed column too, which i defined in demo.

CREATE TABLE Employee_Test
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2),
alldata nvarchar(100) not null
--allData as Convert(varchar(10), [Emp_ID]) +'|'+ Emp_name +'|'+Convert(varchar(10), Emp_sal)

create TRIGGER trgInsteadOfinsert ON Employee_Test
	declare @id int;
	declare @nm Varchar(100);
	declare @Sal Decimal (10,2);
	declare @adata nvarchar(100);
	select @id = max(et.Emp_Id) from Employee_Test et;
	--set @nm = convert(varchar(10),@id);
	if(isnumeric(@id) = 0)
	set @id= 1;
    Set @id = @id+1;  
	select @nm = i.Emp_Name from inserted i;
	select @Sal = i.Emp_Sal from inserted i;
	select @adata= Convert(nvarchar(100),@id)+'|'+i.Emp_name+'|'+Convert(nvarchar(100),i.Emp_sal)
	 from inserted i;
	INSERT INTO Employee_Test VALUES (@nm,@Sal,@adata);

INSERT INTO Employee_Test(Emp_name,Emp_Sal) VALUES ('ravi',1000);
INSERT INTO Employee_Test(Emp_name,Emp_Sal) VALUES ('kishan',1200);
INSERT INTO Employee_Test(Emp_name,Emp_Sal) VALUES ('pradeep',1100);

select * from Employee_Test

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900