How to convert numeric to nvarchar in SQL Server?

Question

Consider the following table. I use a trigger to add to the table. In the column of converting the number to the string, it fails.

CREATE TABLE [dbo].[tblAIAgent]
(
    [AgentCode] [NVARCHAR](10) NOT NULL,
    [NationalCode] [BIGINT] NOT NULL 
         CONSTRAINT [DF_tblAIAgent_NationalCode]  DEFAULT ((0)),
    [FirstName] [NVARCHAR](50) NOT NULL 
         CONSTRAINT [DF_tblAIAgent_Name] DEFAULT (''),
    [LastName] [NVARCHAR](50) NOT NULL 
         CONSTRAINT [DF_tblAIAgent_Family] DEFAULT (''),
    [IsActive] [BIT] NOT NULL 
         CONSTRAINT [DF_tblAIAgent_Active] DEFAULT ((1)),
    [Counter] [INT] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_tblAIAgent] 
        PRIMARY KEY CLUSTERED 
)

ALTER TRIGGER [dbo].[AgentInsert] 
ON [dbo].[tblAIAgent]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @AgentCode NVARCHAR(10)
    DECLARE @NationalCode BIGINT
    DECLARE @FirstName NVARCHAR(50)
    DECLARE @LastName NVARCHAR(50)
    DECLARE @IsActive BIT
    DECLARE @CounterIs INT

    SET @CounterIs = @@IDENTITY

    SELECT
        @AgentCode = AgentCode,
        @NationalCode = NationalCode,
        @FirstName = FirstName,
        @LastName = LastName,
        @IsActive = IsActive 
    FROM inserted

    INSERT INTO tblAIAgent (NationalCode, FirstName, LastName, IsActive, AgentCode)
    VALUES (@NationalCode, @FirstName, @LastName, @IsActive, 'Agent_' + CAST(@CounterIs AS NVARCHAR(4))) 
END

Answer 1

You have a few problems here:

The @@IDENTITY is a system function contains the last identity value that is generated when an INSERT, SELECT INTO, or BULK COPY statement is completed. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.

In your case, you have an INSTEAD OF INSERT trigger, so there is no INSERT.

This below query is completely wrong and will gives wrong results, it works as expected only if one row inserted, if there is more than 1 row, then those variables will hold just the values of one row, and you will lose the other values of the other rows, cause the pseudo INSERTED may contains 1 or more rows

select @AgentCode=AgentCode,
       @NationalCode=NationalCode,
       @FirstName=FirstName,
       @LastName=LastName,
       @IsActive=IsActive 
from inserted

Now, looking to your table, you already have an IDENTITY column, so you don't need to a TRIGGER at all, you can just make a computed column as

CREATE TABLE [dbo].[tblAIAgent](
    [AgentCode] AS 'Agent_' + CAST([Counter] AS VARCHAR(10)),
    [NationalCode] [bigint] NOT NULL 
    CONSTRAINT [DF_tblAIAgent_NationalCode]  DEFAULT ((0)),
    [FirstName] [nvarchar](50) NOT NULL 
    CONSTRAINT [DF_tblAIAgent_Name]  DEFAULT (''),
    [LastName] [nvarchar](50) NOT NULL 
    CONSTRAINT [DF_tblAIAgent_Family]  DEFAULT (''),
    [IsActive] [bit] NOT NULL 
    CONSTRAINT [DF_tblAIAgent_Active]  DEFAULT ((1)),
    [Counter] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [PK_tblAIAgent] PRIMARY KEY ([Counter])
    );

---

UPDATE:

According to your comment "a computed column can no longer be selected as the PK. I want this column to be placed in other relevant tables as a FK.I wrote the trigger to get the column value instead of the computed column so that I can select the column as the primary key". You are trying to make it a PRIMARY KEY so you can do as

CREATE TABLE T(
  Counter INT IDENTITY(1,1) NOT NULL,
  OtherCol INT,
  Computed AS CONCAT('Agent_', CAST(Counter AS VARCHAR(10))) PERSISTED,
  CONSTRAINT PKT PRIMARY KEY(Computed)
);

CREATE TABLE TT(
  ReferenceComputedColumn VARCHAR(16) NOT NULL,
  OtherColumn INT,
  CONSTRAINT FK_ReferencedComputedColumn 
      FOREIGN KEY(ReferenceComputedColumn) 
      REFERENCES T(Computed)
)

INSERT INTO T(OtherCol) VALUES
(1), (2), (3);

INSERT INTO TT(ReferenceComputedColumn, OtherColumn) VALUES
('Agent_1', 10),
('Agent_3', 20);

SELECT *
FROM T LEFT JOIN TT 
ON T.Computed = TT.ReferenceComputedColumn;

See how it's working.

See also this article Properly Persisted Computed Columns by Paul White.