knAHadm1

1DELIMITER $$
2
3DROP TRIGGER IF EXISTS insert_customer_status_history_row$$
4CREATE TRIGGER insert_customer_status_history_row BEFORE UPDATE ON mobile_app_customer_data FOR EACH ROW BEGIN
5    IF NEW.customer_status != OLD.customer_status THEN
6        INSERT INTO mobile_app_status_history (user, customer_status) VALUES (NEW.mobile_app_id, NEW.customer_status);
7    END IF;
8END$$
9
10DELIMITER ; 
11     
12DROP TABLE mobile_app_customer_data;
13CREATE TABLE IF NOT EXISTS mobile_app_customer_data (
14    mobile_app_id int(11) NOT NULL AUTO_INCREMENT UNIQUE,
15    crm_id bigint,
16    other_crm_id bigint,
17    auth_id int(11) UNIQUE,
18    referred_by int(11),
19    first_name varchar(50) NOT NULL,
20    last_name varchar(50) NOT NULL,
21    phone_number varchar(10) NOT NULL,
22    email varchar(100) NOT NULL,
23    customer_status int(11) NOT NULL,
24    date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
25    address text,
26    FOREIGN KEY (auth_id) REFERENCES mobile_app_auth(auth_id),
27    FOREIGN KEY (referred_by) REFERENCES mobile_app_auth(auth_id),
28    FOREIGN KEY (customer_status) REFERENCES mobile_app_crm_statuses(status_id),
29    PRIMARY KEY (mobile_app_id)
30); 
31     
32DROP TABLE mobile_app_status_history;
33CREATE TABLE IF NOT EXISTS mobile_app_status_history (
34    history_id int(11) NOT NULL AUTO_INCREMENT UNIQUE,
35    user int(11) NOT NULL,
36    customer_status int(11) NOT NULL,
37    status_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
38    FOREIGN KEY (user) REFERENCES mobile_app_customer_data(mobile_app_id),
39    FOREIGN KEY (customer_status) REFERENCES mobile_app_crm_statuses(status_id),
40    PRIMARY KEY (history_id)
41); 
42     
43UPDATE mobile_app_customer_data SET customer_status = 5 WHERE mobile_app_id = 34; 
44     
45#1054 - Unknown column 'customer_status' in 'field list'