AFTER INSERT Trigger for SQL Server to create unique random 8 character code?

Question

I started with some stored procedure code by Raymond Lewallen for a password generator. I would like to create a trigger that will create a unique 8 character ID each time a new row (a customer) is inserted. What I have so far:

    CREATE procedure dbo.AllAccessIDgenerator (
     
      @showID varchar(40)
      @accessID varchar(100) OUT
    )
    

    As
    

    Begin
       declare @codeLength int
       declare @characters varchar(100)
    
       declare @count int
       set @characters = ''
       set @codeLength = 8

    -- set A - Z (uppercase)
     
     set @count = 65
     
      while @count <=90
     
       begin
         
         set @characters = @characters + Cast(CHAR(@count) as char(1))
         
         set @count = @count + 1
     
       end
     end

    -- set 0-9
    set @count = 48  
     while @count <=57  
     begin  
      set @characters = @characters + Cast(CHAR(@count) as char(1))  
      set @count = @count + 1  
     end  
     end  
 
    set @count = 0  
    set @accessID = ''  

     while @count <= @codeLength
     
      begin
         
       set @accessID = @accessID + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
         
       set @count = @count + 1
     
      end
    
     end

    end
    
    GO

How do I (a) Take a stored procedure and make it a trigger in SQL Server 2008 and (b) If I needed to test for uniqueness, how would I do that?

Answer 1

Given you'r requirements, this is how I would do it

1. Create a table passwords with two columns. Password and IsUsed.
2. Create a unique index on the Password column.
3. Fill this table with as many items as you'd ever need.
4. In your trigger, get the first Password where IsUsed = 0.
5. Set IsUsed = 1.

Obviously, you should use some form of encryption or hashing for your passwords. Passwords should never be stored as plain text. I'd suggest you do a search for methods on password protection.

Edit

1. it's not entirely clear to me if you need passwords or just unique 8 character IDs for your customers. If unique IDs is all you need, you should offcourse disregard the encryption part of this solution.
2. For filling the table, I would generate x amount of IDs into a temporary table and do a select distinct from this table as the source for an insert into the actual table.

Answer 2

To be honest, I'd recommend doing this in your domain layer, rather than in the database. Domain logic in the database can make it hard to maintain and deploy later on. Sure you can have constraints etc in the database to prevent invalid foreign key values etc, but the generation of the unique value is a business rule, and belongs in the business rule layer of your system (i.e. the Domain Layer/Logic/Objects).

Also, what is the reason for generating the unique value this way? Would an identity column suit your purposes better? You could always use the identity column as part of a 8-char padded value, which would ensure uniqueness.

Answer 3

I'm going to add some words of advice about using a trigger for this. Yes, it is possible to do this with a trigger. (And unlike Neil, I think it should be a database issue because all records will need the uniqueness not just ones added through a specific interface. Putting logic like this in the application is a bad idea from the standpoint of data integrity. Ata minumum make sure the filed has a unique index.)

However, most inexperienced trigger developers think that triggers work on each row individually and do not design their trigger to handle multiple row inserts. You may think that there are no multiple row inserts; you would most of the time be wrong. At some point you are going to want to import a group of customers, the trigger must be able to handle this.

Now using your proc, it can handle only one record at a time. This is a huge flaw because the only way to handle it in a trigger is through the use of a cursor or while loop. This is extremely slow and could end up locking up your customer table for hours if you have the need to add a lot of customers all at once.

I think Lieven's idea of creating a pool of possible unique passwords is a good one because then you can use set-based logic in the trigger to fill it out. But to work it correctly, you would need three columns (an identity field to use for grabbing the next 300 rows for instance). You should also schedule a nightly job to generate more potential passwords if the number unused is below a certain percentage. That way things will never fail because you unexpectedly ran out of potential passwords.