Purging the audit log tables using the posted example?
I have been using a description of a SQL purge function outlined here as the basis for a SQL function to purge the event log (nxp_logs) by username and date.
It appears the example deletes entries in the nxp_log, nxp_logs_extinfo and nxp_logs_mapextinfos tables using a temporary table based on the references to nxp_logs table contained in the nxp_logs_extinfos and nxp_extinfo tables. The temporary table is used as the baseline list of what to delete in each of the three tables above.
If an entry in the nxp_logs table does not have a link in the nxp_logs_mapextinfos or the nxp_logs_extinfo tables then that entry on nxp_logs will not be deleted even though it might meet the data criteria used when creating the original temporary “baseline” table.
Question: Do I need to make another “delete” pass through the nxp_log table after the “delete” pass based on the temporary table build originally to delete the entries which meet the purge criteria and do NOT have references to the nxp_logs_mapextinfos table?
I've looked through the nxp_log table and some of the entries in the table are NOT referenced by the nxp_logs_mapextinfos table and therefore are not picked up in the baseline temporary table.
Thanks, in advance for any SQL insights you may have.