How to change the value of a table column if one record is deleted

Hi Guys,

I have an employee table with columns empID, empname, company. I have created a section to intake empid and delete the corresponding record in the table DB. My empids run like 1,2,3 etc. If I delete empID 1 then the next record's empID value in the table should automatically become 1 and the record 3's empID should become 2 and so on. Is this possible? If yes, please tell me how.

Help is very much appreciated!!

***Edited by Moderator Marissa to move from Pega Academy to Pega Support Community; update platform capability tags***


Keep up to date on this post and subscribe to comments

November 13, 2019 - 12:19pm

Besides exploring a way to do this in Pega, have you thought of writing a database trigger ON/AFTER DELETE(depending on what DB you use) from the table you mention where you put in your own DML logic of manipulating the value of the empid column?

November 14, 2019 - 5:22am

This spec seems unusual. Normally you'd want to always have unique ids, so as you can put historic information together. Switching ID's on the fly (reusing them) would make that kind of information unreliable.

Is there a reason that you want to do this?

November 19, 2019 - 3:38am

Can you try using a page list for employee information and point your employee ID to the index of the page list. If you delete any of the records (Ex-EmployeeInfo(1)) then the employee ID of all other records gets updated.

November 26, 2019 - 3:59pm

I will echo Laurence's question, this seems like a very bad idea. If the IDs are referenced anywhere else, all of those references would need to be updated every time you did this, or they would be invalid.

What is your motivation for doing this?