File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes help with SQL Server Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "help with SQL Server" Watch "help with SQL Server" New topic

help with SQL Server

Eduardo Bueno
Ranch Hand

Joined: Jun 04, 2009
Posts: 155
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.
Campbell Ritchie

Joined: Oct 13, 2005
Posts: 46375
Don't know your answer, but you appear to have posted in the correct place. You are however more likely to get good answers if you let people know what the thread is about
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Try using @@indentity instead of the value from inserted. This should be the last identity value used.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
I agree. Here's the link:
subject: help with SQL Server
It's not a secret anymore!