First of all, this is a pure tsql and SQL Server issue. If this is not the right place, or non java matters can't be discussed in the Ranch, sorry for the inconvenience.
I need to build a table with an specific kind of primary key. The table is 'person':
Here is the matter: the primary key (id) will be the initial letter of the name + the initial letter of the city + the age + the identity number (cont). For example, when I insert ('eduardo', 'araraquara', 19) the generated PK will be 'ea191'. the second record ('bear', 'ranch', 30) will be 'br302', and so on.
I have never worked with triggers, until I was told this can be done with it. I managed to create this one:
But when I insert the above records in the table I get the following IDs: 'ea190', 'br300'. As you can see, the command 'SELECT cont FROM inserted' is returning the value 0. The identifier is being generated after the record is inserted. Does anyone know how can I work on this issue? Recall that I don't know much about triggers, if I'm doing anything wrong please notify me. I hope the matter is quite clear and understanding.