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.