Trigger password reset delay

Posted on 2005-04-20
Last Modified: 2010-03-19
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

Question by:hcrejazz
    LVL 25

    Expert Comment

    One thing you'll have problems with is an update statement that changes multiple rows.  The trigger only fires once for the multiple updates.
    LVL 28

    Accepted Solution

    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.

    Author Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now