DEV Community: Harish T

Cursor + Trigger

Harish T — Mon, 06 Oct 2025 12:52:53 +0000

In this post, we'll explore two powerful tools in relational databases:

Cursors — for iterating row-by-row over query results

Triggers — for automating logic in response to data changes

We’ll cover what they are, how to use them, and example use-cases.

What Is a Cursor?

A cursor is a database object that lets you process query results row by row, instead of processing the entire result set at once. This is useful when you need to run procedural logic for each row, e.g. for validations, formatting, or complex computations.

Employees Table
CR EATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary INT
);

INSERT INTO Employee VALUES
(1, 'Arjun', 45000),
(2, 'Priya', 60000),
(3, 'Kiran', 75000),
(4, 'Meera', 48000),
(5, 'Rahul', 90000);

Output

Cursor That Fetches High-Salary Employees

Let’s say you want to print out names of employees whose salary exceeds 50,000.

CODE:

DECLARE
CURSOR high_salary_cursor IS
SELECT EmpName
FROM Employee
WHERE Salary > 50000;

v_name Employee.EmpName%TYPE;
BEGIN
OPEN high_salary_cursor;
LOOP
FETCH high_salary_cursor INTO v_name;
EXIT WHEN high_salary_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END LOOP;
CLOSE high_salary_cursor;
END;

What Is a Trigger?

A trigger is a named procedure that fires automatically in response to certain events on a table — INSERT, UPDATE, or DELETE. Triggers are useful when you want to enforce rules, maintain audit logs, or replicate changes.
Students + Audit Table

Create Student and Audit Tables
CREATE TABLE Studt (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID VARCHAR(10),
StudentName VARCHAR(50),
ActionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE TRIGGER student_insert_audit
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName)
VALUES (:NEW.StudentID, :NEW.StudentName);
END;

Insert Data into Studt
INSERT INTO Students VALUES ('S01', 'Arjun');
INSERT INTO Students VALUES ('S02', 'Priya');
INSERT INTO Students VALUES ('S03', 'Kiran');

SELECT * FROM Student_Audit;

When & Why to Use Cursors and Triggers
Cursor
Use Case: Row-by-row logic (e.g. complex processing per record)
Caution: Can be slow on large datasets — try set-based SQL where possible.
Trigger
Use case: Automatic enforcement (audit logs, validations, replication)
Caution: Make triggers simple and predictable, debugging can get tricky

Use cursors when procedural logic is hard or impossible to express purely with SQL.

Use triggers to ensure data consistency, enforce business rules, or maintain history without manual invocation.

Normalization In DBMS

Harish T — Mon, 06 Oct 2025 05:11:34 +0000

Simplifying Database Normalization: From 1NF to 3NF
When you start designing a database, it’s easy to store everything in one big table.
But soon, you’ll face duplicate data and inconsistency issues.
That’s where normalization comes in — the process of organizing data step-by-step to make it efficient and logical.

In this post, we’ll learn 1NF, 2NF, and 3NF using a simple Student–Course example.

1NF – All Data in One Table
We start with a single table that contains students, their courses, and instructors.

Problem: Instructor names and course data repeat multiple times.
Let’s fix that.

2NF – Splitting Data into Logical Tables

Now we’ll separate the data into Students, Courses, and a linking table StudentCourses.

Table for Students

Table for Courses

Table for Instructors

Table for Studentcourses

3NF – Removing Transitive Dependencies
emove transitive dependency (non-key attributes shouldn’t depend on other non-keys).

👉 In our case, the InstructorPhone depends on Instructor, not directly on Course.
So we already separated Instructors, which makes this 3NF compliant.

Final tables in 3NF:

Students(StudentID, StudentName)
Instructors(Instructor, InstructorPhone)
Courses(CourseID, CourseName, Instructor)
StudentCourses(StudentID, CourseID)

Final Combined View

Now we can join everything neatly using an SQL JOIN.

Key Takeaways
By breaking down our initial table step by step:

1NF → Atomic values
2NF → Remove partial dependencies
3NF → Remove transitive dependencies
We achieved a clean, normalized schema that eliminates anomalies and improves consistency.

ACID Properties in Database

Harish T — Mon, 06 Oct 2025 04:59:04 +0000

Mastering ACID Properties in Databases with SQL
Every reliable database follows the ACID principles Atomicity, Consistency, Isolation, and Durability to ensure data remains accurate and dependable even under system failures.

In this blog, we’ll demonstrate ACID properties with SQL transactions using a simple Loan Management example.

Step 1: Setup the Schema

We’ll create a Loans table that stores customer loan detail

Enter the values to be inserted in the table

Display

Atomicity — “All or Nothing”

Concept: Either all statements in a transaction succeed, or none are applied
Update and roll back

Atomicity ensures no partial updates happen.

Consistency — “Valid Data Always”

Concept: The database should always remain in a valid state before and after a transaction.

Consistency ensures that invalid records (like negative dues) are never stored.
Isolation — “Transactions Work Independently”

Concept: When multiple users perform transactions at the same time, one should not see another’s uncommitted data.

Isolation ensures accurate reads — no dirty or uncommitted data is visible.

Durability — “Data That Stays Forever”
Concept: Once a transaction is committed, it remains even after a crash or restart.

Key Takeaways

Property | Meaning | SQL Feature Demonstrated

Transactions, Deadlocks & Log Based Recovery

Harish T — Mon, 06 Oct 2025 04:47:18 +0000

Understanding Transactions, Deadlocks, and Log-Based Recovery in SQL
Working with databases involves more than just storing and retrieving data. To ensure your applications are reliable, consistent, and recoverable, you need to understand transactions, deadlocks, and log-based recovery. Let’s explore these concepts step by step using a simple Accounts table.

Setting Up the Database

We’ll start with a simple table to simulate bank accounts

Transaction – Atomicity & Rollback

Imagine transferring 500 from Alice to Bob. Using a transaction ensures that either both changes happen, or none happen

START TRANSACTION;

UPDATE Accounts
SET balance = balance - 500
WHERE name = 'Alice';

ROLLBACK;

UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';

Simulating Deadlocks

Deadlocks happen when two transactions block each other. Open two SQL sessions:
Session 1
START TRANSACTION;
SELECT * FROM Accounts WHERE name='Alice' FOR UPDATE;
UPDATE Accounts SET balance = balance + 100 WHERE name='Bob';

Session 2
START TRANSACTION;
SELECT * FROM Accounts WHERE name='Bob' FOR UPDATE;
UPDATE Accounts SET balance = balance - 100 WHERE name='Alice';

RESULT:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Deadlocks occur when two transactions wait for each other indefinitely. Always design transactions to acquire locks in the same order to prevent them.

Log-Based Recovery

Most modern databases like MySQL (binary logs) or PostgreSQL (WAL) automatically log all transactions. This allows you to undo operations if needed.

Key Takeaways

Transactions ensure that operations are atomic and consistent.
Deadlocks can happen when multiple transactions compete for resources; detect and prevent carefully.
Log-based recovery guarantees that your database can undo or redo transactions for durability and consistency.

Indexing,Hashing, and Query

Harish T — Mon, 06 Oct 2025 04:30:42 +0000

Create a table

NSERT INTO Students VALUES
(101, 'Arun', 'CSBS', 8.5),
(102, 'Mathan', 'ECE', 8.8),
(103, 'Karthik', 'MECH', 6.9),
(104, 'Hareesh', 'CSE', 9.1),
(105, 'Ravi', 'EEE', 7.2),
(106, 'Srivishal', 'CSBS', 8.8),
(107, 'Vignesh', 'IT', 8.0),
(108, 'Harish', 'CSE', 9.3),
(109, 'Deepak', 'ECE', 7.5),
(110, 'Nidheesh', 'CSBS', 9.0),
(111, 'Pradeep', 'MECH', 6.8),
(112, 'Lokhitha', 'EEE', 7.6),
(113, 'Raj', 'CSBS', 8.7),
(114, 'Divya', 'IT', 8.4),
(115, 'Saravanan', 'CSE', 9.2),
(116, 'Monika', 'ECE', 7.9),
(117, 'Ganesh', 'MECH', 6.7),
(118, 'Kavya', 'CSBS', 9.1),
(119, 'Surya', 'EEE', 7.3),
(120, 'Anitha', 'IT', 8.2);

Create a B-Tree Index on roll_no
CREATE INDEX idx_rollno ON Students(roll_no);

Query Using B-Tree Index

SELECT * FROM Students WHERE roll_no = 110;

Create a B+ Tree Index on cgpa

SELECT * FROM Students WHERE cgpa > 8.0;

Query Using Hash Index

SELECT * FROM Students WHERE dept = 'CSBS';

CRUD Operation in MongoDB

Harish T — Mon, 06 Oct 2025 03:56:39 +0000

Create (Insert):-

Insert at least 5 student records into the students collection.

We cannot start by inserting 5 student records into our students collection together. We can create each student as separate document.

Code:-

{
student_id: "S001",
name: "Harshitha",
age: 20,
department: "CSBS",
year: 2,
cgpa: 9
}

{
student_id: "S002",
name: "Madhummitha",
age: 21,
department: "CSBS",
year: 3,
cgpa: 8.5
}

{
student_id: "S003",
name: "Lokhitha",
age: 20,
department: "MI",
year: 3,
cgpa: 8
}

{
student_id: "S004",
name: "Hareesh",
age: 22,
department: "IT",
year: 3,
cgpa: 9.2
}

{
student_id: "S005",
name: "Dhaaraneesh",
age: 20,
department: "CIVIL",
year: 2,
cgpa: 8.8
}
{
student_id: "S006",
name: "Harish",
age: 22,
department: "CSE",
year: 3,
cgpa: 9.2
}

READ(QUERY)

Find all students with CGPA > 8.

db.students.find({ cgpa: { $gt: 8 } })

Find students belonging to the Computer Science department.

db.students.find({ department: "CSE" })

UPDATE

Update the CGPA of a specific student.

{ "student_id": "S005" }

Delete:-

Delete one student record by student_id.

{ "student_id": "S004" }

RESULT

Conclusion:-

In this blog, we explored how to perform CRUD operations in MongoDB using a real-world example of a student database. Starting from inserting records, querying based on conditions, updating multiple documents, and finally deleting specific records – we’ve covered the foundation of working with MongoDB.

CRUD operations form the building blocks of every application, whether you’re managing users in a website, products in an e-commerce app, or students in a college system.

Thanks to Mr.@santhoshnc for encouraging us in exploring new paths in technology and gain knowledge.

assignment using db.io

Harish T — Wed, 27 Aug 2025 11:53:25 +0000