Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trigger password reset delay

Posted on 2005-04-20
3
Medium Priority
?
255 Views
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.
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


0
Comment
Question by:hcrejazz
3 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 13828679
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
 
LVL 28

Accepted Solution

by:
rafrancisco earned 375 total points
ID: 13828688
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
 

Author Comment

by:hcrejazz
ID: 13828900
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

810 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