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.

CREATE TRIGGER AccountUnlock on UserAccount
If UPDAte(Disable)
           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
         'Else do nothing and end trigger because user account isn't disabled

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.
One thing you'll have problems with is an update statement that changes multiple rows.  The trigger only fires once for the multiple updates.
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.
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.