CODEWITHSUNDEEP
asp.net-mvc-3sql-server-2008stored-procedurestriggers
shennyL
shennyL

Reputation: 2794

SQL Server - How to auto-delete "old" database records?

I have a database table which storing shop list for users. I wish to store only 12 shop list per user, means if currently user1 has 12 records in the table, once user1 create a new shop list, the 1st shop list (oldest) will be deleted and the new shop list will be stored.

The ShopList table consist of ShopListID (PK), UserID (FK) and a LastUpdatedDate will is updated by a trigger once user insert/delete any shoplist item belong to the shoplist.

I got no idea how to do this at all.. is it using trigger? or stored procedure? really need help here...

Appreciate any feedback.. Thanks...

Upvotes: 1

Views: 4587

Answers (3)

Yannis
Yannis

Reputation: 6157

I have come across this problem recently and it really depends on your "archiving" strategy.

What I have done is that I created a stored procedure that selects the records to be archived element onwards for every user account (my requirement is very similar to yours in the sense that i have to select the 31st element onwards in a user account). I can also give you some code here if you think it will come in handy.

I have created an extra table called XXXX_archive which is a clone of the schema on your shopping_list table(s). This is to insert old, archived records there in case a user asks to retrieve his list in the future (this is obviously optional but would come in handy).

The stored procedure finds this records and inserts them in the XXXX_archive table and then deletes them from the XXXX. This runs on a nightly basis (or whenever you feel its necessary) through the SQL Server Agent.

The effect is that the 13th element is not deleted the moment that the user creates another shopping list but i think thats fine cause you are in charge of your archiving strategy and can describe it in your TOS.

Just thought I should write my experience here cause i sorted out this problem just days ago.

EDIT: My stored proc is as follows:

INSERT into shopping_lists_archive
    SELECT *
    FROM shopping_lists 
    WHERE id in (
        select id
        from (
            SELECT ROW_NUMBER() OVER ( 
               PARTITION BY user_ID  
               ORDER BY user_ID desc) AS RowNumber,  
            id, user_ID
            FROM shopping_lists c
            where c.user_ID in (select USER_ID from shopping_lists group by user_id having COUNT(1) > 12)
        ) t
        where rownumber > 12
    )

DELETE FROM shopping_lists 
        WHERE id in (
            select id
            from (
                SELECT ROW_NUMBER() OVER ( 
                   PARTITION BY user_ID  
                   ORDER BY user_ID desc) AS RowNumber,  
                id, user_ID
                FROM shopping_lists c
                where c.user_ID in (select USER_ID from shopping_lists group by user_id having COUNT(1) > 12)
            ) t
            where rownumber > 12
        )

There you go - it may be slightly different than what you need cause i m archiving based on a join between two tables and had to amend my original query to your requirement.

Upvotes: 1

RubbleFord
RubbleFord

Reputation: 7646

I'd personally change the select query to only select the top 12 so that will control what the user can see.

I'd then use a database job that runs on a schedule that deletes the ones that you don't want.

Upvotes: 1

Adam Tuliper
Adam Tuliper

Reputation: 30152

You can do this via a trigger or a procedure. You can also in your service layer/ business ligic layer query for the count there upon a save and remove the old records as well. Im for the business logic approach as it's more testable and keeps business logic out of triggers or procedures , so my recommendation is a code based approach.

Upvotes: 2

Related Questions