Password Generating Trigger Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Password Generating Trigger Problem

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-10 : 06:02:10

Hello All,

I had created a trigger which will auto generate the password and insert in a column name "password", this password can be later changed by user's own password, but it's not working:

CREATE TRIGGER Insert_password
ON dbo.student_form
FOR INSERT
AS
INSERT INTO dbo.student_form (password)
select
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)
from
	(select ch =
	  replicate('ABCDEFGHJKLMNPQURSUVWXYZ',6)+
	  replicate('abcdefghjkmnpqursuvwxyz',6)+
	  replicate('23456789',7) ) a

Please help!

Daipayan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 06:17:21

Without knowing structure of table student_form it is not possible to give a reliable help.

No, you're never too old to Yak'n'Roll if you're too young to die.

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-10 : 06:27:53

student_form table has following column:

CREATE TABLE student_form (
    form_ID numeric NOT NULL CONSTRAINT prim_stul PRIMARY KEY,
    student_name varchar(100) NOT NULL,
    password  varchar(10) NOT NULL,
    student_dob  datetime NOT NULL,
    student_email varchar(100) NOT NULL)
GO

Daipayan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 07:28:42

Seems you want to update the table


CREATE TRIGGER Insert_password
ON dbo.student_form
FOR INSERT
AS
update dbo.student_form 
set password=
(
select
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)+
	substring(ch,convert(int,rand()*len(ch)-1),1)
from
	(select ch =
	  replicate('ABCDEFGHJKLMNPQURSUVWXYZ',6)+
	  replicate('abcdefghjkmnpqursuvwxyz',6)+
	  replicate('23456789',7) ) a
) where form_ID =(select form_ID from inserted)

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 07:31:13

Oh!
I would do a join on inserted!

No, you're never too old to Yak'n'Roll if you're too young to die.

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-10 : 23:54:08

Thanks for this reply..

Daipayan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-11 : 00:26:38

quote:
Originally posted by daipayan

Thanks for this reply..

Daipayan

Have you got it worked?

Madhivanan

Failing to plan is Planning to fail

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-11 : 00:32:19

Yes, I got it worked, then I played with the code created another password generation trigger:

CREATE TRIGGER Insert_password
ON dbo.student_form
AFTER INSERT
AS
SET NOCOUNT ON
UPDATE dbo.student_form set spassword =
CASE ROUND(1 + (RAND() * (1)), 0) WHEN 1 THEN CHAR(ROUND(97 + (RAND() * (25)), 0)) ELSE CHAR(ROUND(65 + (RAND() * (25)), 0)) END + 
CASE ROUND(1 + (RAND() * (2)), 0) WHEN 1 THEN CHAR(ROUND(97 + (RAND() * (25)), 0)) WHEN 2 THEN CHAR(ROUND(65 + (RAND() * (25)), 0)) ELSE CHAR(ROUND(48 + (RAND() * (9)), 0)) END + 
CASE ROUND(1 + (RAND() * (2)), 0) WHEN 1 THEN CHAR(ROUND(97 + (RAND() * (25)), 0)) WHEN 2 THEN CHAR(ROUND(65 + (RAND() * (25)), 0)) ELSE CHAR(ROUND(48 + (RAND() * (9)), 0)) END + 
CASE ROUND(1 + (RAND() * (2)), 0) WHEN 1 THEN CHAR(ROUND(97 + (RAND() * (25)), 0)) WHEN 2 THEN CHAR(ROUND(65 + (RAND() * (25)), 0)) ELSE CHAR(ROUND(48 + (RAND() * (9)), 0)) END + 
CASE ROUND(1 + (RAND() * (2)), 0) WHEN 1 THEN CHAR(ROUND(97 + (RAND() * (25)), 0)) WHEN 2 THEN CHAR(ROUND(65 + (RAND() * (25)), 0)) ELSE CHAR(ROUND(48 + (RAND() * (9)), 0)) END + 
CASE ROUND(1 + (RAND() * (2)), 0) WHEN 1 THEN CHAR(ROUND(97 + (RAND() * (25)), 0)) WHEN 2 THEN CHAR(ROUND(65 + (RAND() * (25)), 0)) ELSE CHAR(ROUND(48 + (RAND() * (9)), 0)) END
where spassword is null

Daipayan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-11 : 00:36:56

You can also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/03/27/filling-random-passwords-set-based-approach.aspx

Madhivanan

Failing to plan is Planning to fail

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-11 : 00:46:53

sure..

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources