title: Many-to-many relationship: how to delete records from 3 table?
body: <p>For example, I have the following tables:</p>
  
  <pre><code>person          person_address            address
  ==========      ==================        ===========
  pid   (PK)      pid           (FK)        aid    (PK)
  ...             aid           (FK)        ...
  </code></pre>
  
  <p>I've set <code>pid</code> to <code>ON DELETE CASCADE</code> and <code>aid</code> to <code>ON DELETE NO ACTION</code>. The goal is to delete the according record from <code>address</code> if a <code>person</code> gets deleted.</p>
  
  <p>I have tried trigger:</p>
  
  <pre><code>CREATE TRIGGER pa_delete 
  AFTER DELETE ON person_address
  FOR EACH ROW 
  DELETE FROM address
  WHERE person_address.address_id = address.address_id
  </code></pre>
  
  <p>But when I delete the <code>person</code> record, <code>address</code> record is still there. What am I doing wrong?</p>
  
  <p><strong>EDIT</strong></p>
  
  <hr>
  
  <p>Tables involved:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `chkdcrm`.`person` (
    `person_id` INT(11) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(45) NOT NULL,
    `last_name` VARCHAR(45) NOT NULL,
    `tel` VARCHAR(20) NULL,
    `fax` VARCHAR(20) NULL,
    `mobile` VARCHAR(20) NOT NULL UNIQUE,
    `email` VARCHAR(150) NOT NULL UNIQUE,
    PRIMARY KEY (`person_id`))
  ENGINE = InnoDB;
  
  CREATE TABLE IF NOT EXISTS `chkdcrm`.`address` (
    `address_id` INT(11) NOT NULL AUTO_INCREMENT,
    `type` ENUM('Invoice', 'Shipping', 'Site', 'Mailing') NOT NULL,
    `street` VARCHAR(100) NOT NULL,
    `city` VARCHAR(100) NOT NULL,
    `state` VARCHAR(30) NOT NULL,
    `zip` VARCHAR(10) NOT NULL,
    `country` VARCHAR(40) NOT NULL,
    PRIMARY KEY (`address_id`))
  ENGINE = InnoDB;
  
  CREATE TABLE IF NOT EXISTS `chkdcrm`.`person_address` (
    `address_id` INT(11) NOT NULL,
    `person_id` INT(11) NOT NULL,
    PRIMARY KEY (`address_id`, `person_id`),
    CONSTRAINT `fk_pa_address`
      FOREIGN KEY (`address_id`)
      REFERENCES `chkdcrm`.`address` (`address_id`)
      ON DELETE NO ACTION,
    CONSTRAINT `fk_pa_person`
      FOREIGN KEY (`person_id`)
      REFERENCES `chkdcrm`.`person` (`person_id`)
      ON DELETE CASCADE)
  ENGINE = InnoDB;
  </code></pre>