SPROC generate different password with Bulk Insert

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 2005 Forums

Transact-SQL (2005)

SPROC generate different password with Bulk Insert

Author

Topic

ahloh
Starting Member

1 Post

Posted - 2009-02-08 : 01:01:28

Hi,

I have an issue here. I have created a FOR INSERT,UPDATE trigger which will generate a new password and update on the password col when col LastReset is inserted or updated with current time. The issue is, this trigger only work when you issue a one row statement. When you issue a bulk insert like "UPDATE tblA set col1='bla' where id in (select id from tblA where col2 is not null)" this will in fact update the password col with the first password generated.

My guess is that for bulk insert, after each insert statement, the trigger should fire. Or otherwise?

the trigger is as follow:


CREATE TRIGGER ResetPwd_tblLogins_byInsertUpdate ON tblLogins 
--this trigger generate/reset a new password
for INSERT,update
AS
	--this is a password reset
	if update (LastReset) 
 		begin
			declare @pwd as varchar(8)
			EXEC sprocRandomPassword 8, 8, @pwd OUTPUT
			update tblLogins set Password=@pwd 
				from inserted 
				where tblLogins.id=inserted.id
			set @pwd=''
		end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 01:13:28

for bulk insert, if you have FIRE_TRIGGERS set, it will fire trigger once which will have all inserted records details in inserted table. so if you want a new password to be created for each where lastreset was set, then you need to loop over each records where lastreset was updated and call sprocRandomPassword for each and assign generated password. for looping you need cusrsor or while loop

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-08 : 01:14:17

The trigger only fires once for a statement no matter how many rows the statement affected.

You may be better off converting the stored procedure to a function so you can run the function for each row inserted/updated:


CREATE TRIGGER ResetPwd_tblLogins_byInsertUpdate ON tblLogins 
--this trigger generate/reset a new password
for INSERT,update
AS
	--this is a password reset
	if update (LastReset) 
 		begin
			update tblLogins set Password=dbo.udf_RandomPassword(8, 8)
				from inserted 
				where tblLogins.id=inserted.id
		end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-09 : 09:23:33

To generate random password, you can also use
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/20/random-password-generator.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources