IDENTITY

Esta é uma dúvida comum entre desenvolvedores.

Em muitas situações, você deseja saber o código que acabou de inserir.

Para isso precisamos saber o IDENTITY, mas qual deles? Existem 3 opções.

SCOPE_IDENTITY() - Retorna o último ID inserido dentro daquele escopo(Procedure, Função, etc).
Recomendo sempre utilizar este.

 IDENT_CURRENT('nomedatabela') - Retorna o último ID inserido em uma determinada tabela, independente de conexão, escopo, etc.

Muito Cuidado com esta, pois você pode pegar o ID de outro insert.

@@IDENTITY - Retorna o último ID inserido na conexão atual, independente de tabela.

Evite usar este, a chance de ter problema é enorme.
Vamos aos testes.

CRIAÇÃO DO AMBIENTE:
CREATE TABLE Cliente
(
     CodCliente int identity(1,1) primary key,
     Nome varchar(50)
)
GO

CREATE TABLE Registro
(
     CodReg int identity(1,1) primary key,
     Nome varchar(50),
     Data datetime default getdate()
)
GO

INSERT INTO Registro(Nome) values('Teste1'),('Teste2')
GO

CREATE TRIGGER  tg_Cliente ON Cliente
For Insert
AS
    INSERT INTO Registro(Nome)
    SELECT Nome FROM inserted
GO

Verifique o cenário atual:
SELECT * from Cliente
SELECT * from Registro





 --Como não tem nenhum registro na tabela Cliente, deve retornar Código 1

INSERT INTO Cliente(Nome) VALUES ('José')
SELECT Scope_Identity() as CódigoCliente

--O último registro inserido foi 1, então ele deve retornar o Código 2

INSERT INTO Cliente(Nome) VALUES ('João')
SELECT Ident_Current('Cliente') as CódigoCliente

--O último registro inserido foi 2, então ele deveria retornar o Código 3, porém ele pega o Código 5(da tabela Registro), que foi inserido pela trigger, pois está na mesma conexão.

INSERT INTO Cliente(Nome) VALUES ('Manuel')
SELECT @@IDENTITY as Código




Muito cuidado ao utilizar estes comandos, analisem bem a situação.