What is a good solution to log the deletion of a row in MySQL?
- by hobodave
Background
I am currently logging deletion of rows from my tickets table at the application level. When a user deletes a ticket the following SQL is executed:
INSERT INTO alert_log (user_id, priority, priorityName, timestamp, message)
VALUES (9, 4, 'WARN', NOW(), "TICKET: David A. deleted ticket #6 from Foo");
Please do not offer schema suggestions for the alert_log table.
Fields:
user_id - User id of the logged in user performing the deletion
priority - Always 4
priorityName - Always 'WARN'
timestamp - Always NOW()
message - Format: "[NAMESPACE]: [FullName] deleted ticket #[TicketId] from [CompanyName]"
NAMESPACE - Always TICKET
FullName - Full name of user identified by user_id above
TicketId - Primary key ID of the ticket being deleted
CompanyName - Ticket has a Company via tickets.company_id
Situation/Questions
Obviously this solution does not work if a ticket is deleted manually from the mysql command line client. However, now I need to.
The issues I'm having are as follows:
Should I use a PROCEDURE, FUNCTION, or TRIGGER? -- Analysis:
TRIGGER - I don't think this will work because I can't pass parameters to it, and it would trigger when my application deleted the row too.
PROCEDURE or FUNCTION - Not sure. Should I return the number of deleted rows? If so, that would require a FUNCTION right?
How should I account for the absence of a logged in user? -- Possibilities:
Using either a PROC or FUNC, require the invoker to pass in a valid user_id
Require the user to pass in a string with the name
Use the CURRENT_USER - meh
Hard code the FullName to just be "Database Administrator"
Could the name be an optional parameter? I'm rather green when it comes to sprocs.
Assuming I went with the PROC/FUNC approach, is it possible to outright restrict regular DELETE calls to this table, yet still allow users to call this PROC/FUNC to do the deletion for them?
Ideally the solution is usable by my application as well, so that my code is DRY.
