Creating a column which should be auto generated by concatenating values in other columns in a table

I got a table in a SQL database with four columns: ID, Comp_Id, Cont_Id and Comp_Cont_Id. The values into ID, Comp_Id, Cont_Id columns are entered manually and the value in the column Comp_Cont_Id is to be generated automatically by concatenating the values in ID, Comp_Id, Cont_Id columns.

For example if we enter (201, 301, 401) into开发者_如何学Python (ID, Comp_Id, Cont_Id) respectively, the value 201_301_401 should be auto-generated in the Comp_Cont_Id column of the table.

Is there a way to achieve it? If so please tell me.

MS SQL supports computed columns, the syntax is given in this similar question.

DROP TABLE test99
CREATE TABLE test99 (
    id int,  
    comp_id int,  
    cont_id int,
    comp_cont_id AS cast(id AS varchar) + '_' + cast(comp_id AS varchar) + '_' + cast(cont_id AS varchar)
) 

INSERT INTO test99 (id, comp_id, cont_id)
VALUES (201, 301, 401)

SELECT * FROM test99

Are you using PHP or ASP.NET to enter the values? If the user is directly keying into the table, you can try using a trigger to set the last value.

However, Comp_Cont_Id is what is called a derived field. Its value depends on other fields. Examples of derived fields are the total number of records, average of prices and etc. It is better to come up with the field on the fly, dynamically, rather than to store it. Of course, there are exception to this, such as when performance is important.