MariaDB – How to Configure Logging for Row Deletions Only

deletelogmariadbtrigger

I'm investigating some rows/tables that intermittently go missing on a Magento site, and of course there's no entries in the error log. (I suspect Magento queries/rules or human error might be the culprit) I know I can enable the General log, but I don't want to log EVERYTHING as this site processes a huge amount of sales every day and the log would be massive.

I'm hoping someone might know of a way to configure either the general log or log table to record only DELETE commands. Open to any suggestions you might have of a better way to go about this.

Best Answer

This is mostly copied from the docs:

Trigger Example - Logging deletions

Scenario: We want to keep a log file containing data from rows that have been deleted from the BOOKS Table. Here's a Trigger definition that accomplishes this:

CREATE TRIGGER Books_Delete
AFTER DELETE ON Books                 /* See note 1 */
  REFERENCING OLD ROW AS Old          /* See note 2 */
FOR EACH ROW                          /* See note 3 */
  INSERT INTO Books_Deleted_Log       /* See note 4 */
      VALUES (Old.title);             /* See note 5 */
  1. The Trigger action has to be AFTER, since the Trigger action includes a SQL-data change statement. You should also replace Books with your table name
  2. It is conventional to use the alias "Old" or "Old_Row" for the old row.
  3. No log will occur for a DELETE statement that affects zero rows.
  4. Replace Books_Deleted_Log with the name of the table you're logging the deleted rows into. Make sure this table has all the columns you need to log the information you need.
  5. OLD is an alias for a single old row, so OLD.TITLE is the scalar value derived from the TITLE Column of that old row. You will have to change Title to a column you want to track. You can also add more columns by following the Old.<column name> pattern using commas as separators.
Related Question