title: SQL trigger before delete not properly inserting rows into audit table
body: <p>I've been staring at this code and looking online to see what's been going on but I can't seem to find what is wrong.</p>
  <p>When I run the code below the update trigger works as intended and inserts data into audit table. When I try to test the delete trigger the delete happens as intended but no rows are added to the audit table.</p>
  <p>None of my google searching is getting me a concrete answer.</p>
  <p>Here is the code for the triggers and testing the triggers:</p>
  <pre><code>--14. PART 1: write stored triggers for update and deletion (idk how many I'm assuming 2)
  CREATE TRIGGER Before_Person_Update
      BEFORE UPDATE ON Person
      FOR EACH ROW
  INSERT INTO PersonHistoryTracker
  SET action = 'update',
      personNumber_FK = OLD.personNumber,
      firstName = OLD.firstName,
      lastName = OLD.lastName,
      DOB = OLD.DOB,
      phoneNumber = OLD.phoneNumber,
      email = OLD.email,
      streetNumber = OLD.streetNumber,
      streetName = OLD.streetName,
      city = OLD.city,
      state = OLD.state,
      zipcode = OLD.zipcode,
      isACustomer = OLD.isACustomer,
      isAnEmployee = OLD.isAnEmployee,
      isAPublisherRep = OLD.isAPublisherRep,
      changedat = NOW();
  
  
  CREATE TRIGGER Before_Person_Delete
  BEFORE DELETE ON Person
  FOR EACH ROW
  INSERT INTO PersonHistoryTracker
  SET action = 'delete',
      personNumber_FK = OLD.personNumber,
      firstName = OLD.firstName,
      lastName = OLD.lastName,
      DOB = OLD.DOB,
      phoneNumber = OLD.phoneNumber,
      email = OLD.email,
      streetNumber = OLD.streetNumber,
      streetName = OLD.streetName,
      city = OLD.city,
      state = OLD.state,
      zipcode = OLD.zipcode,
      isACustomer = OLD.isACustomer,
      isAnEmployee = OLD.isAnEmployee,
      isAPublisherRep = OLD.isAPublisherRep,
      changedat = NOW();
  
  
  SHOW TRIGGERS;
  
  
  SELECT personNumber, firstName, lastName, streetNumber, streetName, city, state
  FROM Person
  WHERE personNumber = &quot;007&quot;;
  
  UPDATE Person
  SET
      lastName = &quot;Johnson&quot;
  WHERE personNumber = &quot;007&quot;;
  
  
  
  SELECT personNumber, firstName, lastName, streetNumber, streetName, city, state
  FROM Person
  WHERE personNumber = &quot;007&quot;;
  
  
  
  SELECT * 
  FROM Person;
  DELETE FROM Person
  WHERE personNumber = &quot;006&quot;;
  SELECT * 
  FROM Person;
  
  --14. PART 2: SHOW THAT TRIGGERS WORK AS INTENDED
  SELECT * 
  FROM PersonHistoryTracker;
  
  </code></pre>
  <p>Here is the code for the audit table:</p>
  <pre><code>-- -- -- -- -- -- -- -- -- PersonHistoryTracker -- -- -- -- -- -- -- -- -- 
  
  -- Creating the PersonHistoryTracker Table
  -- Primary Keys: newChangeStartDate, personNumber_FK
  -- Foreign Key: personNumber_FK
  
  CREATE TABLE PersonHistoryTracker
  (
      changeNum INT DEFAULT 0,
      personNumber_FK VARCHAR(5),
      firstName VARCHAR(30) NOT NULL,
      lastName VARCHAR(30) NOT NULL,
      DOB DATE,
      phoneNumber VARCHAR(10),
      email VARCHAR(50),
      streetNumber VARCHAR(10),
      streetName VARCHAR(30),
      city VARCHAR(30),
      state CHAR(2),
      zipcode CHAR(5),
      isACustomer BOOLEAN,
      isAnEmployee BOOLEAN,
      isAPublisherRep BOOLEAN,
      action VARCHAR(30),
      changedat DATETIME,
  
      CONSTRAINT PersonHistoryTracker_PK PRIMARY KEY (changeNum, personNumber_FK),
  
      CONSTRAINT PersonHistoryTracker_FK FOREIGN KEY (personNumber_FK)
      REFERENCES Person(personNumber) ON DELETE CASCADE
      
  );
  </code></pre>
  <p>*****UPDATE:</p>
  <p>I have tried to create a new delete trigger that follows another table and audits that tables information when deleted both the old and new triggers don't work on delete and I'm not sure why I have seen that it could potentially relate to a bug where cascade operations are not handled properly in mySQL but i'm not sure any help would be appreciated.</p>