Automating Banking Operations Inside the Database

Every software system begins with optimism. Ours is no different. We're building a banking system, and the first requirement is embarrassingly simple: Allow customers to withdraw money from their accounts. The database schema is straightforward. We have an "Accounts" table containing an account number and its current balance.

When a customer withdraws ₹5000, the application executes: UPDATE Accounts SET Balance = Balance — 5000 WHERE Account_id = 101; Problem solved. Or is it? Before that, let's ask ourselves what exactly a withdrawal is. At first glance, it would seem like updating a number. But is it so?

Before any money leaves the bank account, the system must answer several questions: Does the account actually exist? Is the account active? Does withdrawal violate the minimum balance rule? Does the account have sufficient balance? Should this transaction be recorded before or after updating the balance? Suddenly, our one-line SQL query no longer represents a withdrawal but one of the steps in this business operation.

SQL is an excellent manipulative language, but intentionally declarative. It describes what the data should do, but not how a business should unfold. A withdrawal, though, is a series of business decisions. It involves conditions, validation, multiple SQL statements, and error handling. In short, it requires programming logic.

To express this procedural or programming logic inside the database, we use PL/SQL (Procedural Language / Structured Query Language).

BEGIN UPDATE Accounts SET Balance = Balance — 5000 WHERE Account_ID = 101;

COMMIT; END; / Now, the database will no longer be asked to execute a single statement. It can execute an entire business operation.

But Where Will The Logic Live?

PL/SQL gives us the ability to describe a withdrawal as a program. But it still does not answer the architectural question.

One option would be to keep PL/SQL code in every application. The banking website. The Mobile App. Branch softwares. ATM software. Initially, this does not seem reasonable — because every application can independently decide how to perform a withdrawal. Every new policy update by the bank will require updating every copy of that program. And every forgotten copy will eventually become a future bug.

This leads to an important engineering principle: Business rules that must be identical across every application should have only one implementation. The database already serves every banking application. Why shouldn't it own the withdrawal logic? This is exactly the role of a stored procedure. A stored procedure is a named program inside the database itself. Instead of sending dozens of SQL statements from the application, it asks the database itself to perform a well-defined operation.

CREATE PROCEDURE WithdrawMoney ( p_account_id IN NUMBER, p_amount IN NUMBER ) AS BEGIN UPDATE Accounts SET Balance = Balance — p_amount WHERE Account_ID = p_account_id; END; /

We have centralised one of the bank's most critical operations. The architecture has become simpler, more consistent, and easier to maintain.

Sometimes We Just Want to Know Something.

One morning the product team begins designing a customer dashboard for the mobile app. The first question it asks is surprisingly simple: "What is my current account balance?" Nothing needs to be updated. No money is moving No transaction needs to be recorded. The application simply wants one piece of information.

Our first instinct might be to write another stored procedure. It certainly works. But writing a procedure for every query starts to feel awkward because procedures are designed to perform work, not produce value. Here's where the database offers another abstraction. A stored function represents a computation whose primary purpose is to return a value. A stored procedure says, "Execute this business operation." A stored function says, "Compute this value for me."

CREATE FUNCTION GetBalance ( p_account_id NUMBER ) RETURN NUMBER AS v_balance NUMBER; BEGIN SELECT Balance INTO v_balance FROM Accounts WHERE Account_ID = p_account_id;

RETURN v_balance; END; /

At this point, our banking system can perform operations through stored procedures and compute values through stored functions.

One Customer is Easy. One Million Customers Are Not.

As the bank grows, the management decides to introduce a new requirement. At the end of every month, interest must be credited to every savings account. Calculating interest for a single account is straightforward. The database already knows the balance. It knows the interest rate. A stored function can calculate this in milliseconds.

But the bank doesn't have a single savings account. It has hundreds of thousands. That means the system needs to repeat the same sequence of operations thousands of times. Sometimes, an engineering problem is not about a set. It is about visiting each member of the set.

Imagine an accountant sitting with a stack of account files. She opens one file → processes it → puts it aside → opens another file → processes it → repeats until the whole stack is completed.

We need a similar mechanism. This is exactly what a cursor provides. A cursor acts like a bookmark over the result of a query. Instead of receiving every row at once, the database allows us to move through the result one record at a time.

Retrieve all accounts → open cursor → fetch first account → calculate interest → update balance → fetch second account → repeat → close cursor

The database allocates a context area — a region of memory that stores information about the SQL query being executed. The cursor acts as a handle to this context, allowing the program to remember where it is in the result set as it processes one row after another.

CURSOR c_accounts IS SELECT Account_ID, Balance FROM Accounts WHERE Account_Type = 'Savings';

OPEN c_accounts; LOOP FETCH c_accounts INTO v_id, v_balance; EXIT WHEN c_account%NOT FOUND; // calculate interest here END LOOP; CLOSE c_accounts;

For many everyday SQL statements, the database creates such cursors internally and automatically. This is called an implicit cursor. When engineers need explicit control over how cursors move through the rows, they define an explicit cursor.

Monthly interest calculation becomes an automated batch operation rather than a manual operation. The architecture has evolved again.

Some Things Should Never Be Forgotten

The compliance team asks a simple question, "Can you show us every withdrawal in the last six months?"

Fortunately, every application always records withdrawals in an Audit_Log table. A few days later, the report says some withdrawals have no corresponding audit log.

After a few investigations, the engineer discovers the problem. Most developers remembered to insert a row into the audit log table after updating the account balance. One developer didn't. He simply forgot to write the audit records. The database never complained because no rule required it.

Why are we relying on developers to remember something that must happen every single time? The obvious solution was to shift the responsibility to the database itself.

Instead of asking every application to remember to create an audit record, the database automatically performs the actions whenever a withdrawal changes the Accounts table. This is the role of a trigger. A trigger is a piece of code that executes automatically in response to a database event such as INSERT, UPDATE, or DELETE.

CREATE TRIGGER AuditWithdrawal AFTER UPDATE OF Balance ON Accounts FOR EACH ROW BEGIN INSERT INTO Audit_log(Account_ID) VALUES(: NEW.Account_ID); END; / Triggers make the database enforce business behavior.

Some rules are bigger than any one table.

Months later, a new requirement arrives — not from developers, but from regulators. The central bank introduces a policy: The total cash reserve maintained across all branches must never exceed the approved limit.

At first glance, this sounds like another validation rule. But where should it be enforced? A CHECK constraint seems an obvious choice. But unfortunately, a CHECK constraint validates the values of a single row.

But the rule spans the entire database. In other words, the constraint isn't local. To represent these database-wide invariants, relational databases define assertions. An assertion is a condition that must always remain true for the database as a whole. It describes the properties of the entire system, not just individual records.

Now, the database protects global business properties.

The Database Must Know Who You Are

The security team asks one final question. Who is actually allowed to perform these operations?

Clearly, not everyone should have the same level of access. Our first idea is straightforward. Grant permissions directly to every employee. When a new teller joins the bank, assign the necessary privileges. When another employee leaves, revoke them. This works. For a while.

But what happens when the bank hires thousands of employees? It soon becomes an administrative nightmare. The bank does not care about individual employees. It cares about job responsibilities. Every teller needs the same permissions. Every manager needs another set. Instead of assigning permissions to people, we assign permissions to roles. A role is simply a named collection of privileges. The individual permissions themselves are known as privileges.

Some privileges apply to specific database objects, such as the ability to SELECT, INSERT, UPDATE, or DELETE rows from a table. Others are system-wide privileges, such as creating new tables, granting permissions, or managing the database objects.

Role: CREATE ROLE Teller; Privileges: GRANT SELECT, UPDATE on Accounts to Teller; Assign Role: GRANT Teller to Ravi;

Now, the administrator does not assign dozens of individual permissions. They instead assign a role. The security becomes easier to manage and less prone to human error.

Our Engineering Journey

Keeping logic inside the database provides consistency, security, and easier maintenance because every application shares the same implementation. However, this approach also has trade-offs. Excessive business logic can make the database harder to maintain, increase the workload on the database server, and reduce portability if the organization later migrates to a different DBMS

One design philosophy becomes clear: not all logic belongs to the application, and not all logic belongs to the database. We moved only those responsibilities to the database that directly concern the integrity and consistency of the data.

A well-engineered system therefore strikes a balance: the database enforces data integrity, transactional correctness, auditing, and security, while the application remains responsible for user interfaces, workflows, and presentation logic.

Engineering Interview Questions (Bonus)

What is PL/SQL, and how does it differ from SQL? What are the main sections of a PL/SQL block? What is the difference between a stored procedure and a function? When should you use a stored procedure instead of application code? What is a cursor, and when is it appropriate to use one? What are implicit and explicit cursors? What is a trigger? Name the different types of triggers. What are the advantages and disadvantages of using triggers? What are assertions, and why are they uncommon in commercial databases? What is the difference between roles and privileges? What is the difference between object privileges and system privileges? How do GRANT and REVOKE work? Why are transactions important in a banking transfer procedure? How would you implement an audit trail for salary changes or money transfers? Which business rules would you enforce in the database, and which would you keep in the application layer?