This was a project for Ruby Tuesday, and some of it is not public domain. As such, I have no source code available for this project.
This project was one of my more interesting ones. I had never written SQL stored procedures prior to this, nor has I dealt with cursors (most of our work doesn’t need them). The problem I was solving is that we never purged old employee data. If someone worked at a store, their basic information was still in our database years later. This caused larger databases, and led to more confusion for our level 1 support team (more for them to sort through, multiple records for an employee, and etc).
We decided to automate this process based on the time since the record was deactivated, using a configurable time frame. The challenges to this project were determining what to delete, as the sequence number of the employee is a foreign key for several other tables, finding a way to continue on error, because if an employee has recent sales data, it cannot be deleted, and packaging it to run automatically at end of day.
Because I had never written a stored procedure, continuing on error and packaging it proved to be the more complicated aspects; certain things would work in the console, but not when wrapped in the procedure, especially with regards to continuing on error. This is where I had to use a cursor, and exception handling (Try/Catch statements), even though the exception handler was blank (we just wanted to keep going on error).
Finding out what data to delete was just a matter of deleting an employee in the tool Micros provided, and using the database logs to see exactly what changed. I then combined that with removing every reference in the custom tables we keep (which do not have foreign keys to deal with).
The process was then added to the database as an end of day step, and now runs every night. Now, we no longer have bloated employee data tables, and when we have to fix an employee record, we no longer have to deal with duplicate records.
Recent Comments