CODEWITHSUNDEEP
sqlsql-servert-sql
Netfangled
Netfangled

Reputation: 2081

Delete record 24 hours after insert

Is there a way to automatically delete a row 24 hours after its creation in Transact-SQL?

I'm making a site (learning experience) where the user needs to click a validation link sent by e-mail once they register. I want the users to validate themselves within 24 hours.

I suppose what I'd need is a trigger, but I'm really not sure on the syntax, nor if it is even possible.

Upvotes: 0

Views: 2292

Answers (2)

paulsm4
paulsm4

Reputation: 121799

Q: Is there a way to automatically delete a row 24 hours after its creation in Transact-SQL?

A: Sure. Write a "sqlcmd" script, wrap it in a .bat file, and invoke it from Windows Scheduled Tasks:

Alternatively, depending on your version, you could schedule the same SQL script from SQL Server Agent:

Putting a different spin on things:

  • When the user clicks your link, you can check if the current time (with respect to MSSQL) is >> 24 hours. If so, you'll reply with a "Too late" message (rather than validating the entry).

In any case - you absolutely, completely, totally, do NOT want to use a trigger!

Upvotes: 2

Lock
Lock

Reputation: 5522

I'm not sure of your schema but I would do it a different way. I would have a date/time against the database record that corresponds to the validation link. When they click the link, verify that the date and time of the database record is within 24 hours of the current time. If so, allow it, otherwise reject it.

Upvotes: 17

Related Questions