MySQL - Trigger example

 A bank table contains id, name, accno, acctype, balance columns and bank_tranactions table contains id, accno, trtype, amount columns. accno in bank table is primary key and accno in bank_transactions table is foreign key. create a trigger when a row is inserted in bank_transactions table to update the bank tables balance.

Here are the **DDL (Data Definition Language)** queries to create the three tables: `bank`, `bank_transactions`, and `transaction_log`.

### 1. `bank` Table:

This table stores basic information about the bank account, including the account number, account type, and balance.

```sql

CREATE TABLE bank (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    accno INT NOT NULL UNIQUE,

    acctype ENUM('SAVINGS', 'CURRENT') NOT NULL,

    balance DECIMAL(10, 2) DEFAULT 0.00

);

```

- **`id`**: A unique identifier for each account (primary key, auto-incremented).

- **`name`**: The name of the account holder.

- **`accno`**: The account number (unique for each customer).

- **`acctype`**: Account type, which can be `SAVINGS` or `CURRENT` (using an ENUM).

- **`balance`**: The balance in the account, starting at 0.00.

---

### 2. `bank_transactions` Table:

This table stores transaction details related to accounts in the `bank` table.

```sql

CREATE TABLE bank_transactions (

    id INT AUTO_INCREMENT PRIMARY KEY,

    accno INT NOT NULL,

    trtype ENUM('DEPOSIT', 'WITHDRAWAL') NOT NULL,

    amount DECIMAL(10, 2) NOT NULL,

    transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (accno) REFERENCES bank(accno) ON DELETE CASCADE

);

```

- **`id`**: A unique identifier for each transaction (primary key, auto-incremented).

- **`accno`**: The account number associated with the transaction (foreign key from the `bank` table).

- **`trtype`**: The type of transaction, either `DEPOSIT` or `WITHDRAWAL`.

- **`amount`**: The amount of the transaction.

- **`transaction_time`**: A timestamp for when the transaction occurred (defaults to the current time).

- **Foreign Key**: `accno` is a foreign key that references the `accno` column in the `bank` table. `ON DELETE CASCADE` ensures that if an account is deleted from the `bank` table, its transactions are also deleted automatically.

---

### 3. `transaction_log` Table:

This table stores logs of all transactions for auditing purposes.

```sql

CREATE TABLE transaction_log (

    id INT AUTO_INCREMENT PRIMARY KEY,

    accno INT NOT NULL,

    trtype ENUM('DEPOSIT', 'WITHDRAWAL') NOT NULL,

    amount DECIMAL(10, 2) NOT NULL,

    transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

```

- **`id`**: A unique identifier for each log entry (primary key, auto-incremented).

- **`accno`**: The account number associated with the transaction.

- **`trtype`**: The type of transaction (`DEPOSIT` or `WITHDRAWAL`).

- **`amount`**: The amount of the transaction.

- **`transaction_time`**: The timestamp when the transaction occurred.

- **`log_time`**: The timestamp when the log entry was created or last updated (automatically updated when a log is modified).

---

### Putting It All Together:

These three tables together can represent a simple banking system where:

- The `bank` table contains account information.

- The `bank_transactions` table records individual transactions.

- The `transaction_log` table records logs of all transactions for auditing or historical purposes.

### Foreign Key Constraints:

- The `accno` column in the `bank_transactions` table has a **foreign key** constraint pointing to the `accno` column in the `bank` table, ensuring that transactions can only be recorded for valid accounts.

You can now use these tables as a foundation for managing bank accounts and their transactions.

=====================================================================

INSERT INTO bank (name, accno, acctype, balance)

VALUES 

('John Doe', 1001, 'SAVINGS', 5000.00),

('Jane Smith', 1002, 'CURRENT', 12000.50),

('Michael Johnson', 1003, 'SAVINGS', 3000.75),

('Emily Davis', 1004, 'SAVINGS', 6500.00),

('David Wilson', 1005, 'CURRENT', 10000.00),

('Linda Brown', 1006, 'SAVINGS', 4000.50),

('James Taylor', 1007, 'CURRENT', 1500.00),

('Patricia Anderson', 1008, 'SAVINGS', 2500.25),

('Robert Thomas', 1009, 'CURRENT', 8200.30),

('Mary Jackson', 1010, 'SAVINGS', 7600.00);

INSERT INTO bank_transactions (accno, trtype, amount)

VALUES (1001, 'DEPOSIT', 100);

INSERT INTO bank_transactions (accno, trtype, amount)

VALUES (1001, 'WITHDRAWAL', 50);

=====================================================================

To create a trigger that automatically updates the `balance` column in the `bank` table when a transaction is inserted into the `bank_transactions` table, we can define a trigger that checks the transaction type (`trtype`). Depending on whether the transaction is a deposit or withdrawal, the balance will be updated accordingly.

Assuming:

- `trtype` will have values like 'DEPOSIT' and 'WITHDRAWAL'.

- The `amount` represents the value of the transaction.

### SQL Trigger Code

```sql

DELIMITER $$

CREATE TRIGGER update_balance

AFTER INSERT ON bank_transactions

FOR EACH ROW

BEGIN

  -- Check if the transaction type is a deposit or withdrawal

  IF NEW.trtype = 'DEPOSIT' THEN

    -- Update the balance by adding the deposited amount

    UPDATE bank

    SET balance = balance + NEW.amount

    WHERE accno = NEW.accno;

  

  ELSEIF NEW.trtype = 'WITHDRAWAL' THEN

    -- Update the balance by subtracting the withdrawn amount

    UPDATE bank

    SET balance = balance - NEW.amount

    WHERE accno = NEW.accno;

  

  END IF;

END$$

DELIMITER ;

```

### Explanation:

- **Trigger Name**: `update_balance`.

- **Trigger Timing**: `AFTER INSERT` (triggered after a new transaction is inserted in `bank_transactions`).

- **Transaction Type Logic**:

  - If the `trtype` is `'DEPOSIT'`, the amount is **added** to the current balance in the `bank` table.

  - If the `trtype` is `'WITHDRAWAL'`, the amount is **subtracted** from the current balance.

- The `WHERE accno = NEW.accno` ensures the correct account is updated based on the account number from the newly inserted transaction (`NEW.accno`).

### Example Workflow:

1. A new transaction (e.g., deposit of $100) is inserted into the `bank_transactions` table:

   ```sql

   INSERT INTO bank_transactions (accno, trtype, amount)

   VALUES (12345, 'DEPOSIT', 100);

   ```

2. The trigger will automatically add $100 to the `balance` of the account with `accno = 12345` in the `bank` table.

3. If a withdrawal occurs:

   ```sql

   INSERT INTO bank_transactions (accno, trtype, amount)

   VALUES (12345, 'WITHDRAWAL', 50);

   ```

4. The trigger will subtract $50 from the `balance` of the account with `accno = 12345`.

This ensures the `balance` in the `bank` table is always up to date after any transaction is inserted into the `bank_transactions` table.