Automatic delete after n entries in the table for a Keyvalue

srajanbose · New User Joined: 11 Oct 2004 Posts: 69 Location: chennai

Hi,

I have a requirement to create a History table.Whenever an update or delete is done in few tables through online screen, before update or delete an entry should be there in the history table with the current value in the table with time,user id etc.

But we need to maintain the last 20 entries in the table for a KEY value. Key value in our table is Program Name,status(update,delete),Table name. If we have entry for the 21st time the first entry needs to be deleted. So the history should be maintained only for the last 20 entries.

Please let me know how to achieve this.

Regards,
Rajan BOSE.

dbzTHEdinosauer · Posted: Thu Sep 02, 2010 4:15 pm

how are you determining that you need to INSERT to the History table?

srajanbose · New User Joined: 11 Oct 2004 Posts: 69 Location: chennai

Hi Dick,

If we request a update for a row in table 'A' through online screen before updating the table 'A' we need to move the current value from table 'A' to the history table and then update the table 'A' with new values.

So based on the 'status' value from online screen (If its update then the value of status is 'U' and 'D' for delete) we need to insert the current value in the history table.

Regards,
Rajan BOSE.

dbzTHEdinosauer · Posted: Thu Sep 02, 2010 4:41 pm

so, you already have code in any module that Updates or Deletes from active table.

I would use Trigger on INSERT/UPDATE to the History table, do a count and delete the earliest.