Can anyone advise me on how to efficiently query the history of cases that were deleted or undeleted? I'd like to see the user, date/time, operation (delete/undelete), case number and (if possible) the groups and roles of the user at the time of the operation. I realize the "user groups and roles at the time of the delete/undelete" may not be practical.
I'm not providing the data structures because part of the question is "Which tables are the efficient source of this data?" I don't want to query the audit tables, they are huge and inefficient.
I got one suggestion from a coworker (below) - does anyone have a better approach?
SELECT
CASE
WHEN comment_txt LIKE '%Case Deleted by%' THEN
'Deleted'
WHEN comment_txt LIKE '%Case has been Un-deleted which was originally deleted by %' THEN
'Undeleted'
END Action,
case_num,
cfg_users.user_fullname,
route_date,
comment_txt
FROM
cfg_users,
case_routing,
case_master
WHERE
case_master.case_id = case_routing.case_id
AND case_routing.user_id = cfg_users.user_id
and (comment_txt LIKE '%Case has been Un-deleted which was originally deleted by %' or comment_txt LIKE '%Case Deleted by%')
ORDER BY
route_date desc