Two Laptop Bag*
The moose likes JDBC 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
Bookmark "help with SQL Server" Watch "help with SQL Server" New topic
Author

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
Sheriff

Joined: Oct 13, 2005
Posts: 38003
    
  22
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
Bartender

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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: help with SQL Server
 
Similar Threads
Restore problem
Problem with OJB
Identity function in HSQLDB
JOIN syntax for HQL . Need help!
Adding a bit new info to the jforum_user table when registerting