help with SQL Server

[Eduardo Bueno]

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':

CREATE TABLE [dbo].[person](
	[id] [char](7) NOT NULL PRIMARY KEY,
	[name] [varchar](20) NOT NULL,
	[city] [varchar](20) NOT NULL,
	[age] [tinyint] NOT NULL,
	[cont] [int] IDENTITY(1,1) NOT NULL
)

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:

CREATE TRIGGER tr_insert 
   ON  person
   INSTEAD OF INSERT
AS 
BEGIN
	DECLARE @id char(7), @name varchar(20), @city varchar(20), @age tinyint, @cont int
	SET @name = (SELECT name FROM inserted)
	SET @city = (SELECT city FROM inserted)
	SET @age = (SELECT age FROM inserted)
	SET @cont = (SELECT cont FROM inserted)
	SET @id = substring(@name, 1, 1) + substring(@city, 1, 1) + cast(@age as varchar) + cast(@cont as varchar)
	
	INSERT INTO teste (id, name, city, age) VALUES(@id, @name, @city, @age) 
END
GO

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]

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]

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