SREEEE

-- Create the tables
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    balance DECIMAL(10, 2)
);

CREATE TABLE customer_detail (
    customer_id INT,
    address VARCHAR(100),
    phone_number VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

CREATE TABLE mini_statement (
    customer_id INT,
    transaction_date DATE,
    amount DECIMAL(10, 2),
    description VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

CREATE TABLE micro_statement (
    customer_id INT,
    transaction_id INT,
    transaction_date DATE,
    amount DECIMAL(10, 2),
    description VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

CREATE TABLE deleted_customer (
    customer_id INT,
    deleted_date DATE,
    reason VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

-- Insert data into the customer table
INSERT INTO customer VALUES (100, 'Manu', 12000);
INSERT INTO customer VALUES (101, 'Remya', 2500);
INSERT INTO customer VALUES (102, 'Anu', 13500);
INSERT INTO customer VALUES (103, 'Sonu', 10000);
INSERT INTO customer VALUES (104, 'Achu', 12500);


CREATE TRIGGER after_customer_update
AFTER UPDATE ON customer
FOR EACH ROW
BEGIN
    INSERT INTO customer_detail (acc_no, cust_name)
    VALUES (NEW.acc_no, NEW.cust_name);
END;


CREATE TRIGGER update_customer
BEFORE UPDATE ON customer
FOR EACH ROW
BEGIN
    INSERT INTO mini_statement VALUES (OLD.acc_no, OLD.avail_balance);
END;

UPDATE customer SET avail_balance = 15000 WHERE acc_no = 101;

SELECT * FROM customer;

SELECT * FROM mini_statement;


CREATE TRIGGER insert_new_customer
AFTER INSERT ON customer
FOR EACH ROW
BEGIN
    INSERT INTO mini_statement VALUES (NEW.acc_no, NEW.avail_balance);
END;


CREATE TRIGGER after_delete_store
AFTER DELETE ON customer
FOR EACH ROW
BEGIN
    INSERT INTO deleted_customer (acc_no, cust_name) VALUES
    (OLD.acc_no, OLD.cust_name);
END;