I have a major problem. I have been put in charge of a database that has the following trigger on a reports table.
CREATE TRIGGER "Report_Trig" ON dbo.Reports FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD RepSecID' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT RepID FROM inserted)
UPDATE Reports SET RepSecID = @randc WHERE RepID = @newc
I believe that this trigger creates a random number and inserts it as the primary key when a new record is inserted. Here are a few this trigger has inseted in the "RepSecID" column:
The problem is it has already created a few duplicate numbering sequences! I thought it would not be a problem and that the chances were a billion to none that this would happen so I left it alone. But I have to make sure this DOES NOT CREATE DUPLICATES in the future. Can somebody tell me how to modify the trigger to either check to see if the random number already exsists in the "RepSecID" column already and if so, create a new one, or expand the numbering range higher?
Thanks in advance,