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