Trigger Emergency! Please Help!

Last Post 05 Apr 2002 10:51 PM by arlene. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
arlene
New Member
New Member

--
05 Apr 2002 10:51 PM
Hello,

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:

947202869
224439277

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?

PLEASE HELP!

Thanks in advance,

Arlene
You are not authorized to post a reply.

Acceptable Use Policy