CODEWITHSUNDEEP
sql-serversql-server-2008triggers
sparrow
sparrow

Reputation: 177

Ensure Uniqueness with Triggers

So, I'm trying to use the following trigger so that, when a row in a table is updated, it'll set a specific column to a string of 8 random alphanumeric characters. The trigger works fine except that the result must be unique. It'll be blank before the trigger so this uniqueness is not enforced on the server. The idea is that once a customer is far enough along in a process, they'll get this string but not before as we get plenty of customers who don't get that far.

The trigger: 

CREATE TRIGGER test_rand
ON test_trigger
AFTER UPDATE 
AS
    UPDATE test_trigger set col2=(select
    Random_String =
    substring(x,(abs(checksum(newid()))%36)+1,1)+
    substring(x,(abs(checksum(newid()))%36)+1,1)+
    substring(x,(abs(checksum(newid()))%36)+1,1)+
    substring(x,(abs(checksum(newid()))%36)+1,1)+
    substring(x,(abs(checksum(newid()))%36)+1,1)+
    substring(x,(abs(checksum(newid()))%36)+1,1)+
    substring(x,(abs(checksum(newid()))%36)+1,1)+
    substring(x,(abs(checksum(newid()))%36)+1,1)

from
          (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a) WHERE col2=''; 
GO

this trigger is the test that I'm running with a simple table that only has two columns, col1 and col2. I update col1 for a bunch of rows and col2 gets the seemingly random values, however I know that two rows could get the same number and I'd like to prevent that if possible.

I had thought to do a check in the select statement itself where that string doesn't exist but I haven't been able to come up with a good way to do that.

I'm new to triggers so if there's something simple I'm missing or that should have been searched then I apologize for wasting your time. This might not even be possible for all I know but I figured I should ask before giving up and trying something else.

Also, the reason this is not being done in the script that does the update is that I can't get a straight answer out of the client as to what scripts actually do that. If this isn't possible I'll just write a task to be run by Windows Task Scheduler but I was hoping to do this instead.

MY SOLUTION

Based off what usr told me, I've created the following code which did what I wanted:

CREATE TRIGGER test_rand
ON test_trigger
AFTER UPDATE 
AS
    DECLARE @Ins_Col3 int;
    DECLARE Ins_Curs CURSOR FOR SELECT col3 FROM inserted;
    DECLARE @Ref char(8);

    OPEN Ins_Curs

    FETCH NEXT FROM Ins_Curs
    INTO @Ins_Col3;

    WHILE @@FETCH_STATUS = 0
    BEGIN;
        SET @Ref = LEFT(NEWID(), 8);

        WHILE(EXISTS(SELECT col2 from test_trigger WHERE col2=@Ref))
        BEGIN;
            SET @Ref = LEFT(NEWID(), 8);
        END;

        UPDATE test_trigger SET test_trigger.col2=@Ref FROM inserted WHERE test_trigger.col3=@Ins_Col3;
            FETCH NEXT FROM Ins_Curs
        INTO @Ins_Col3;
    END;

I had to add col3 as an auto-incremented id value so that I could identify each row. col1 is therefore only there so that I have something to change with an update statement.

Upvotes: 1

Views: 617

Answers (2)

aaaa bbbb
aaaa bbbb

Reputation: 3043

You should use a GUID instead of kludging an 8 char string. In SQL Server it is a UniqueIdentifier column type. Here is an article on it: http://msdn.microsoft.com/en-us/library/ms190215(v=sql.105).aspx

Upvotes: 2

usr
usr

Reputation: 171178

If you want to prevent that from happening, include a WHILE loop in your trigger, looping as long as the generated string is not valid due to violating the uniqueness condition:

WHILE 0=0
BEGIN
 SET_TO_NEW_VALUE
 IF(NEW_VALUE_IS_UNIQUE)
  BREAK
END

You can also ROLLBACK and RAISEERROR if the value is not unique. Depends on what you want to happen.

Upvotes: 0

Related Questions