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


hcrejazzAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
I don't think you can use a WAITFOR in a trigger because it will lock the table.  Also, based on Books Online, WAITFOR

"Specifies a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction."

Nothing here mentioned about being allowed to be used in triggers.
0
 
jrb1Commented:
One thing you'll have problems with is an update statement that changes multiple rows.  The trigger only fires once for the multiple updates.
0
 
hcrejazzAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.