Random Password Trigger Help Please

Last Post 09 Jan 2004 01:40 AM by LeoNjampa. 2 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

--
11 Apr 2002 10:36 AM
Hi, hopefully someone knows this already.

I have a trigger that creates a random ID (int) like this:

========================================
CREATE TRIGGER "Report_Trig" ON dbo.Reports FOR INSERT AS
SET NOCOUNT ON

if @@rowcount <> 1 return --
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD RepSecID' */
SELECT @newc = (SELECT RepID FROM inserted)
loop:
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))

if not exists(select 1 from Reports where RepSecID = @randc
begin
UPDATE Reports SET RepSecID = @randc WHERE RepID = @newc
end
else
goto loop
========================================

Is there a way to modify this trigger to mix alphabet characters with the random ID's?

Example: re345dex8

I need something like this for a temporary password on a new insert.

Thanks

Arlene


TonyL_SQL
New Member
New Member

--
15 Apr 2002 06:13 AM
Here's some code that will generate a random 8 character password string, you could substitute this into your trigger for the exisiting statement that generates the random number.


declare @letters char(36)
declare @passwd varchar(8)
declare @i int
declare @j int
select @Letters = 'abcdefghijklmnopqrstuvwxyz0123456789'

set @i = 0
set @passwd = ''
while @i < 9
begin
set @j = convert(int,((36 * rand()) + 1))
set @passwd = @passwd + convert(varchar(1),substring (@letters,@j,1))
set @i = @i + 1
end
select @passwd
LeoNjampa
New Member
New Member

--
09 Jan 2004 01:40 AM
Here's also an SP that will generate and populate a table variable with the stipulated number of 8 character unique alphanumeric password strings.
REM: to do validations and error checking if you use this script.
--------

CREATE PROC dbo.GenerateRandomAlphaNumericIDs
(
@HowMany INT = 500
)

/*
Description: Generates random alphanumeric values
Usage: EXEC dbo.GenerateRandomAlphaNumericIDs

To do: Validation and ERROR checking
*/

AS

DECLARE @Loop INT
DECLARE @NoOfRandomIDs INT
DECLARE @cnt TINYINT
DECLARE @MaxLen TINYINT --len of id
DECLARE @ret int
DECLARE @Code VARCHAR(8)
DECLARE @TblCount INT
DECLARE @TblRandID TABLE (RowID INT IDENTITY(1,1) NOT NULL, RandomID VARCHAR(8) NOT NULL)

SET NOCOUNT ON

SET @NoOfRandomIDs = @HowMany
SELECT @TblCount = COUNT(*) FROM @TblRandID
SET @Loop = 1

WHILE @Loop <= @NoOfRandomIDs AND @TblCount <= @NoOfRandomIDs
BEGIN --outer
SET @cnt=1
SET @MaxLen = 8
SET @Code = ''
WHILE @cnt<=@MaxLen
BEGIN --inner
SELECT @ret = CONVERT(INT, RAND() * POWER(2,8))
IF (@ret <58)
BEGIN
SET @Code=@Code + CASE
WHEN LEN(@ret)=1 THEN CAST(@ret AS VARCHAR)
WHEN LEN(@ret)=2 THEN CAST(@ret % 10 AS VARCHAR)
WHEN LEN(@ret)=3 THEN CAST(@ret % 100 AS VARCHAR)
ELSE '9' --not really neccessary since max @ret is 256 i.e. power(2,8)
END
END
ELSE IF @ret >= 58
BEGIN
SET @Code = @Code + CHAR((CONVERT(INT, RAND() * POWER(2,8)) % 26) + 65) --A=65
END
-- increment
SET @cnt = @cnt+1
END --inner

-- verify that @code is not already in table
IF NOT EXISTS(SELECT * FROM @TblRandID WHERE RandomID = @Code)
BEGIN
INSERT INTO @TblRandID VALUES(@Code)
END
-- check no in table
SELECT @TblCount = COUNT(*) FROM @TblRandID
-- increment @Loop
SET @Loop = @Loop + 1
END --outer

-- Display results
SELECT * FROM @TblRandID

SET NOCOUNT OFF

GO

LeoSeda

You are not authorized to post a reply.

Acceptable Use Policy