hcrejazz
asked on
Trigger password reset delay
I have a simple table which has primary key[userid] column, [disable] column, [failcount] column. I have the main part of the trigger working where it automatically resets the users [failcount] and [disable] to 0. I can't seem to get working is the what if statement and the waitfor delay statement. Example: If disable column isn't updated then do nothing, otherwise pause for 10 minutes and then set disable and failcount to zero values. Any help much appreciated. Its basically a automatic password reset trigger.
Code:
CREATE TRIGGER AccountUnlock on UserAccount
FOR UPDATE
AS
If UPDAte(Disable)
Begin
waitfor delay "0:10:00"
update UserAccount
Set Disable = 0, failcount = 0
From UserAccount as A
Inner Join Inserted as B
on A.UserID = B.UserID
Where coalesce(b.Disable,0) = 1
End
ELSE
Begin
'Else do nothing and end trigger because user account isn't disabled
Return
End
Code:
CREATE TRIGGER AccountUnlock on UserAccount
FOR UPDATE
AS
If UPDAte(Disable)
Begin
waitfor delay "0:10:00"
update UserAccount
Set Disable = 0, failcount = 0
From UserAccount as A
Inner Join Inserted as B
on A.UserID = B.UserID
Where coalesce(b.Disable,0) = 1
End
ELSE
Begin
'Else do nothing and end trigger because user account isn't disabled
Return
End
jrb1
One thing you'll have problems with is an update statement that changes multiple rows. The trigger only fires once for the multiple updates.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah I may be going about is all wrong. I think it might be better to use a timestamp and just update the table every ten minutes and compare the timestamp to a designated value so password resets don't happen until a certain amount of time passed.