Nest Next Door Database

Table names and fields have been changed, and only a few tables are shown.


  CREATE TABLE egg_cartons (
     carton_id 	    INT(6)        NOT NULL AUTO_INCREMENT,
     quant    	    VARCHAR(20),
     post_date 	    DATE,
     order_status   VARCHAR(20),
     PRIMARY KEY    (carton_id)
  );

  CREATE TABLE orders (
     order_id       char(38)      NOT NULL,
     cust_name      VARCHAR(45)   NOT NULL,
     pickup_loc     VARCHAR(45),
     pickup_when    VARCHAR(45),
     cust_email     VARCHAR(45),
     cust_phone     VARCHAR(35),
     order_time     DATETIME,
     order_note     TINYTEXT,
     carton_id      INT(6),
     PRIMARY KEY    (order_id),
     FOREIGN KEY    (carton_id)
        REFERENCES  egg_cartons(carton_id)
        ON DELETE CASCADE
  )ENGINE=InnoDB;

  CREATE TABLE orders_permanent (
     order_num      int(6)        NOT NULL AUTO_INCREMENT,
     order_id       char(38)      NOT NULL,
     cust_name      VARCHAR(45)   NOT NULL,
     pickup_loc	    VARCHAR(45),
     pickup_when    VARCHAR(45),
     cust_email     VARCHAR(45),
     cust_phone     VARCHAR(35),
     order_time     DATETIME,
     order_note     TINYTEXT
  )ENGINE=InnoDB;



  delimiter //
  CREATE TRIGGER save_order_record
  AFTER INSERT
  ON orders
  FOR EACH ROW
  BEGIN
      INSERT INTO orders_permanent
      (order_id, cust_name, pickup_loc, pickup_when, cust_email, cust_phone, order_time, note, carton_id)
      SELECT
      order_id, cust_name, pickup_loc, pickup_when, cust_email, cust_phone, order_time, note, carton_id
      FROM orders
      WHERE reservation_id = NEW.reservation_id;
  END; //