Create a function for generating random number in SQL Server trigger

I have to create a function in a SQL Server trigger for generating random numbers after insert. I want to update the column with that generated random number please help what I have missed in my code.

If you know other ways please suggest a way to complete my task.

This my SQL Server trigger:

ALTER TRIGGER [dbo].[trgEnquiryMaster]
ON [dbo].[enquiry_master]
AFTER INSERT 
AS 
    declare @EnquiryId int;
    declare @ReferenceNo varchar(50);
    declare @GenReferenceNo NVARCHAR(MAX);

    select @EnquiryId = i.enquiry_id from inserted i;
    select @ReferenceNo = i.reference_no from inserted i;
BEGIN
     SET @GenReferenceNo = 'CREATE FUNCTION functionRandom (@Reference VARCHAR(MAX) )
        RETURNS VARCHAR(MAX)
        As
        Begin
        DECLARE @r varchar(8);
        SELECT @r = coalesce(@r, '') + n
        FROM (SELECT top 8 
        CHAR(number) n FROM
        master..spt_values
        WHERE type = P AND 
        (number between ascii(0) and ascii(9)
        or number between ascii(A) and ascii(Z)
        or number between ascii(a) and ascii(z))
        ORDER BY newid()) a

        RETURNS @r
        END
        '

        EXEC(@GenReferenceNo)

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- update statements for trigger here
    UPDATE enquiry_master 
    SET reference_no ='updated' 
    WHERE enquiry_id = @EnquiryId
END   

Answers


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;

A trigger should be very nimble and quick - it is no place to do heavy and time-intensive processing, and definitely no place to create new database objects since (a) the trigger is executed in the context of the code causing it to fire, and (b) you cannot control when and how often the trigger is fired.

You need to

  1. define and create your function to generate that random value during database setup - once, before any operations are executed on the database

  2. rewrite your trigger to take into account that multiple rows could be inserted at once, and in that case, the Inserted table will contain multiple rows which all have to be handled.

So your trigger will look something like this (with several assumptions by me - e.g. that enquiry_id is the primary key on your table - you need this to establish the INNER JOIN between your data table and the Inserted pseudo table:

ALTER TRIGGER [dbo].[trgEnquiryMaster]
ON [dbo].[enquiry_master]
AFTER INSERT 
AS 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- update statements for trigger here
    UPDATE enq
    SET reference_no = dbo.GenerateRandomValue(.....)
    FROM enquiry_master enq
    INNER JOIN inserted i ON enq.enquiry_id = i.enquiry_id  

Need Your Help

Is there a way i can launch another program without having to do a fork()?

c fork

I have a main program written in C, i need it to launch another process in parallel,

top right corner notification HUD window

macos cocoa notifications osx-lion

When the mac application received a notification from APNS,I want to show a notification HUD window on top right corner of screen.