Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kirangentlebreeze1987
Starting Member
14 Posts |
 Posted - 2010-04-06 : 12:47:50
|
| hello friends,i am new to sqlserver.i want to know, how to delete a record from my table automatically after 3o days from now using triggerskkk |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-04-06 : 12:52:11
|
create a sql agent job for this, and schedule it to run dailyDELETE FROM yourTable WHERE DATEDIFF (day, yourDateField, getDate()) > 30 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-06 : 12:55:29
|
| [code]DELETE FROM yourTable WHERE yourDateField < DATEADD(dd,DATEDIFF(dd,0,GETDATE())-30,0)[/code]to make use of an existing index on yourDateField------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-06 : 13:20:56
|
quote:
Originally posted by kirangentlebreeze1987
hello friends,i am new to sqlserver.
In that case, you should consider whether you actually need to delete the record, or whether it would be better to flag it as inactive. For example: Consider an order entry system that has a table OrderType. This table contains only 2 columns (the PK OrderTypeID and OrderType) and three rows identifying the 3 order types supported by the system, which are "Local", "Interstate" and "International". You also have an Order table with many columns, one of which is OrderTypeID. You have multiple Order records for each of the 3 order types. With me so far?Now, suppose that you wish to discontinue support for international orders. If you removed the "International" row from the OrderTypes table, then this would stop any new orders being marked as "International", however there's a down side. Consider the following query:SELECT o.OrderNo, o.DeliveryDate, t.OrderTypeFROM Orders oINNER JOIN OrderTypes t ON o.OrderTypeID = t.OrderTypeID This query used to return every order in the system, but now it doesn't return the international orders. Why? Well you deleted the order type record, so the INNER JOIN won't match those records any more. In fact, if there was a relationship defined, and the delete didn't throw an error, then it means you also deleted all those international orders as well. Clearly, this isn't what you wanted to achieve. However, if you add another column to the OrderTypes table, call it Disabled, and default it to 0, then you can use that column to disable an order type when you're displaying them to the use as new orders are entered. If you set disable = 1 for the international OrderType, then the use won't be able to select this order type in the client application. Any queries on existing orders will still work nicely, and of course, no orders will be deleted. I realise this is probably irrelevant to your current situation, but being that you're new to SQL, I thought I'd mention it. It's a good lesson for newbies.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
|
|
|
|
|
how to delete a record automatically after 30 days