Bank-Transaction-System

A simple SQL-based Bank Transaction System project that demonstrates the use of tables, triggers, locking mechanisms, and transaction validation in a relational database management system. This project is useful for learning advanced SQL concepts such as automatic balance updates, transaction logging, and error handling using triggers.

📌 Features Create and manage bank accounts Deposit and withdraw transactions Automatic balance updates using triggers Transaction logging system Validation for insufficient balance Table locking mechanism for safe updates 🛠️ Technologies Used SQL / MySQL Database Triggers Transaction Management Locking Mechanism

📂 Database Structure

1. Accounts Table

Stores customer account details and balances.

CREATE TABLE accounts ( account_id INT PRIMARY KEY, name VARCHAR(50), balance DECIMAL(10,2) ); 2. Transactions Table

Stores all deposit and withdrawal transactions.

CREATE TABLE transactions ( txn_id INT PRIMARY KEY, account_id INT, amount DECIMAL(10,2), txn_type VARCHAR(10), txn_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 3. Logs Table

Stores transaction logs generated automatically.

CREATE TABLE logs ( log_id INT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(300), log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 📥 Insert Sample Data INSERT INTO accounts VALUES (101, 'Ravi', 1000);

⚡ Triggers

1. Transaction Logging Trigger

Automatically logs every transaction.

DELIMITER $$

CREATE TRIGGER log_transaction AFTER INSERT ON transactions FOR EACH ROW BEGIN INSERT INTO logs(message) VALUES ( CONCAT( 'Transaction of ', NEW.amount, ' ', CASE WHEN NEW.txn_type='Deposit' THEN 'deposited' ELSE 'withdrawn' END, ' for Account ', NEW.account_id ) ); END $$

DELIMITER ; 2. Balance Update Trigger

Automatically updates account balance after transactions.

DELIMITER $$

CREATE TRIGGER update_balance AFTER INSERT ON transactions FOR EACH ROW BEGIN IF NEW.txn_type = 'Deposit' THEN

    UPDATE accounts
    SET balance = balance + NEW.amount
    WHERE account_id = NEW.account_id;

ELSE

    UPDATE accounts
    SET balance = balance - NEW.amount
    WHERE account_id = NEW.account_id;

END IF;

END $$

DELIMITER ; 3. Validation Trigger

Prevents withdrawals when balance is insufficient.

DELIMITER $$

CREATE TRIGGER check_balance BEFORE INSERT ON transactions FOR EACH ROW BEGIN DECLARE current_balance DECIMAL(10,2);

SELECT balance INTO current_balance
FROM accounts
WHERE account_id = NEW.account_id;

IF NEW.txn_type = 'Withdraw'
   AND NEW.amount > current_balance THEN

    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Insufficient Balance!';

END IF;

END $$

DELIMITER ;

🔒 Locking Mechanism

Used to avoid concurrent update issues.

LOCK TABLES accounts WRITE;

UPDATE accounts SET balance = balance + 500 WHERE account_id = 101;

UNLOCK TABLES;

▶️ Example Transactions Deposit Transaction INSERT INTO transactions VALUES (1, 101, 500, 'Deposit', NOW()); Result Balance increases automatically Log entry is created Withdraw Transaction INSERT INTO transactions VALUES (2, 101, 300, 'Withdraw', NOW()); Result Balance decreases automatically Transaction gets logged Invalid Withdraw Transaction INSERT INTO transactions VALUES (3, 101, 5000, 'Withdraw', NOW()); Result ERROR: Insufficient Balance!

📖 Learning Outcomes This project helps understand: SQL Triggers Transaction Management Data Validation Concurrency Control Database Locking Automated Logging

🚀 Future Improvements Add stored procedures Add user authentication Create a frontend interface Generate transaction reports Add multiple account support

👨‍💻 Author Developed as a SQL Database Mini Project for learning and academic purposes.