Trigger or Stored for lock and unlock database for 15 minute

5 pts.
Tags:
SQL Database
SQL Server 2005
SQL Server database
SQL Server triggers
Dear Sir, I create a login form in which if you insert password 3 times wrong then u r account is locked for next 15 minute. and we send a random password  on referenced emailid which is sent after 15 minute. For these 15 minute the account isactive='false'. So can u please help me to give code for a stored procedure or triggers which is fired after 15 minute,which update this account isactive = 'true'. Please help me. Thanks In Advance

Software/Hardware used:
sql
ASKED: March 23, 2011  8:49 AM
UPDATED: March 23, 2011  7:09 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You’ll need a column in your table called “UnlockTime” or something similar which you also set when you disable the account. Then query based on that column with a simple update statement.

<pre>UPDATE YourTable
SET isActive = True, UnlockTime = NULL
WHERE UnlockTime > getutcdate()</pre>

kccrosser:
MRDenny has provided the key, but a little additional info may help.

In the initial login call, add the update statement from MRDenny, so that any accounts that are ready to be re-enabled are re-enabled before attempting to login.

When the third login attempt fails, write/update the account record with the account id, active=false, and the scheduled unlock time.

Personally, I prefer to write the time the lock is established, and then have an environment variable (or a static stored function) that contains the delay time. In that case, the update query would look more like:

<pre>dUnlockTimePoint = getutcdate() – myFunctionForElapsedTime();
UPDATE YourTable
SET isActive = True, UnlockTime = NULL
WHERE UnlockTime > dUnlockTimePoint;</pre>

This approach allows you to change the delay and have it affect pending unlocks immediately. This is more useful when dealing with long term locks, such as password expirations, reminders, etc.

Note that normally you should NOT invoke a function or arithmetic expression involving non-deterministic functions inside the WHERE clause. In a small table like this, it isn’t really a problem, but I try to avoid functions in WHERE clauses, as these can quickly cause table scans.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following