2

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.

1
  • @Erik Thanks for the quick response. To clarify, the Magento admins have stated that an entire category of products "goes missing", I highly doubt the table itself is dropped as they've been able to import the missing products from a backup without too much difficulty. As far as a trigger after a DELETE is concerned, I assume something like an INSERT INTO delete_log would do the trick?
    – Powelly
    Oct 22, 2015 at 2:21

1 Answer 1

2

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.
2
  • Exactly what I was after. Thanks so much for your help, @Erik. I love this community.
    – Powelly
    Oct 22, 2015 at 2:37
  • @Powelly sure thing. Glad I could help.
    – Erik
    Oct 22, 2015 at 2:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.