Create a function for generating random number in SQL Server trigger

To generate random numbers, just call CRYPT_GEN_RANDOM which was introduced in SQL Server 2008:

SELECT CRYPT_GEN_RANDOM(5) AS [Hex],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) AS [HexStringWithout0x],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(10)) AS [Translated-ASCII],
       CONVERT(NVARCHAR(20), CRYPT_GEN_RANDOM(20)) AS [Translated-UCS2orUTF16]

returns:

Hex             HexStringWithout0x    Translated-ASCII    Translated-UCS2orUTF16
0x4F7D9ABBC4    0ECF378A7A            ¿"bü<ݱØï           붻槬㟰添䛺⯣왚꒣찭퓚

If you are ok with just 0 - 9 and A - F, then the CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) is all you need.

Please see my answer on DBA.StackExchange on a similar question for more details:

Password generator function

The UPDATE statement shown in the "Update" section of that linked answer is what you want, just remove the WHERE condition and add the JOIN to the Inserted pseudo-table.

The query should look something like the following:

DECLARE @Length INT = 10;

UPDATE em
SET    em.[reference_no] = rnd.RandomValue
FROM   dbo.enquiry_master em
INNER JOIN Inserted ins
                 ON ins.enquiry_id = em.enquiry_id
CROSS APPLY dbo.GenerateReferenceNo(CRYPT_GEN_RANDOM((em.[enquiry_id] % 1) + @Length)) rnd;

And since the function is slightly different, here is how it should be in order to get both upper-case and lower-case letters:

CREATE FUNCTION dbo.GenerateReferenceNo(@RandomValue VARBINARY(20))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  WITH base(item) AS
  (
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
  ), items(item) AS
  (
    SELECT NULL
    FROM   base b1
    CROSS JOIN base b2
  )
  SELECT (
     SELECT TOP (LEN(@RandomValue))
            SUBSTRING('1234567890QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm',
                      (CONVERT(TINYINT, SUBSTRING(@RandomValue, 1, 1)) % 62) + 1,
                      1) AS [text()]
     FROM   items
     FOR XML PATH('')
  ) AS [RandomReferenceNo];
GO

And please follow the usage shown above, passing in CRYPT_GEN_RANDOM((em.[enquiry_id] % 1) + @Length), not: CRYPT_GEN_RANDOM(@RefferenceNOLength).

Other notes:

• @marc_s already explained the one-row vs multiple-rows flaw and how to fix that.
• not only is a trigger not the place to create a new object (i.e. the function), that function wouldn't have worked anyway since the call to newid() (in the ORDER BY) is not allowed in a function.

• You don't need to issue two separate SELECTs to set two different variables. You could do the following:

  SELECT @EnquiryId = i.enquiry_id,
         @ReferenceNo = i.reference_no
  FROM   TableName i;
  

• Passing strings into a function requires quoting those strings inside of single-quotes: ASCII('A') instead of ASCII(A).

UPDATE

The full Trigger definition should be something like the following:

ALTER TRIGGER [dbo].[trgEnquiryMaster]
ON [dbo].[enquiry_master]
AFTER INSERT
AS
BEGIN
  DECLARE @Length INT = 10;

  UPDATE em
  SET    em.[reference_no] = rnd.RandomValue
  FROM   dbo.enquiry_master em
  INNER JOIN Inserted ins
          ON ins.enquiry_id = em.enquiry_id
  CROSS APPLY dbo.GenerateReferenceNo(
                                       CRYPT_GEN_RANDOM((em.[enquiry_id] % 1) + @Length)
                                     ) rnd;
END;