title: SQL : can't get IDENTITY value in Trigger
body: <p>I am newbie in SQL.I am creating a dummy project.In my project, I have one register page and I want to create a UserName(FirstName + LastName + UserID).But the problem is, I cant catch the UserID value.I have used both After Trigger and Instead Of Trigeer. Below is my script -</p>
  
  <p>Table -</p>
  
  <pre><code>CREATE TABLE UserInfo
  (
      UserID INT IDENTITY(1,1),
      FirstName NVARCHAR(500),
      LastName NVARCHAR(500),
      [Password] NVARCHAR(200),
      EmailID NVARCHAR(200),
      [Address] NVARCHAR(500),
      CountryID INT,
      StateID INT,
      UserName NVARCHAR(500)
  )
  </code></pre>
  
  <p>Proc of Insert Data -</p>
  
  <pre><code>CREATE PROC Create_User    
   @FirstName NVARCHAR(500),    
   @LastName NVARCHAR(500),    
   @Password NVARCHAR(200),    
   @EmailID NVARCHAR(200),    
   @Address NVARCHAR(500),    
   @CountryID INT,    
   @StateID INT,    
   @UserID INT OUTPUT    
  AS    
   BEGIN    
   INSERT INTO UserInfo (FirstName,LastName,[Password],EmailID,[Address],CountryID,StateID)    
   VALUES    
   (@FirstName,@LastName,@Password,@EmailID,@Address,@CountryID,@StateID)    
  
   SET @UserID = SCOPE_IDENTITY();    
  
   END 
  </code></pre>
  
  <p>After Trigger -</p>
  
  <pre><code>CREATE TRIGGER Create_UserName
  ON UserInfo
  AFTER INSERT
  AS
  BEGIN
  DECLARE @_UName VARCHAR(200);
  SET @_UName = (SELECT FirstName+LastName+Cast(UserID AS NVARCHAR(100)) from INSERTED )
  
  INSERT INTO UserInfo (UserName) VALUES (@_UName)
  END
      GO
  </code></pre>
  
  <p>Output - Inserted two rows</p>
  
  <p>Instead Of Trigger -</p>
  
  <pre><code>CREATE TRIGGER Create_UserName
  ON UserInfo
  INSTEAD OF INSERT
  AS
  BEGIN
  DECLARE @UName NVARCHAR(200);
  DECLARE @FName NVARCHAR(200);
  DECLARE @LName NVARCHAR(200);
  DECLARE @UPassword NVARCHAR(200);
  DECLARE @UEmailID NVARCHAR(200);
  DECLARE @UAddress NVARCHAR(200);
  DECLARE @UCountryID INT;
  DECLARE @UStateID INT;
  SET @UName = (SELECT FirstName+LastName+Cast(UserID AS NVARCHAR(100)) from INSERTED )
  SET @FName = (SELECT FirstName from INSERTED )
  SET @LName = (SELECT LastName from INSERTED )
  SET @UPassword = (SELECT [Password] from INSERTED )
  SET @UEmailID = (SELECT EmailID from INSERTED )
  SET @UAddress = (SELECT [Address] from INSERTED )
  SET @UCountryID = (SELECT CountryID from INSERTED )
  SET @UStateID = (SELECT StateID from INSERTED )
  
  
  
  
  INSERT INTO UserInfo (FirstName,LastName,[Password],EmailID,[Address],CountryID,StateID,UserName)  
  VALUES (@FName,@LName,@UPassword,@UEmailID,@UAddress,@UCountryID,@UStateID,@UName)
  END
      GO
  </code></pre>
  
  <p>Output - this works fine but Identity is 0.</p>
  
  <p>Please tell me how I can do this?</p>
  
  <p>Thanks in advance.</p>