Making A Row Delete Itself At A Certain Time?

Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?

Also, I have another question:
when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:


PK-----------Item----------price
1------------XYZ1----------$$$$
2------------XYZ2----------$$$$
3------------XYZ3----------$$$$

when I delete PK 2 this is what happens

PK-----------Item----------price
1------------XYZ1----------$$$$
3------------XYZ3----------$$$$

the PKs are not in consecutive order


What I want is this to happen: when PK 2 is deleted I need it to show as follows

PK-----------Item----------price
1------------XYZ1----------$$$$
2------------XYZ3----------$$$$

The PKs stay in consecutive order.



See how the PK are still in consecutive order?