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 :)
Posted

Might be I am deviating you from the issue. You can think of having computed column with persistence.

http://blog.sqlauthority.com/2010/08/03/sql-server-computed-column-persisted-and%C2%A0performance/[^]
   
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
INSTEAD OF insert
AS
	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)
	begin
	set @id= 1;
	end
	else
	begin
    Set @id = @id+1;  
	end	
	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;
	
	BEGIN
	
	INSERT INTO Employee_Test VALUES (@nm,@Sal,@adata);
	END
GO

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