How to change the value of a table column if one record is deleted
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***
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?
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.
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.
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.