SQL Practical | Library Database Triggers & Java JDBC Connectivity

In this video, we cover two important SQL and Java practicals —
1️⃣ Creating and testing Database Triggers in SQL (Library Management Example)
2️⃣ Implementing MySQL Database Connectivity in Java (JDBC) with navigation operations like Add, Edit, Delete, and Fetch.

This is a complete college practical demo perfect for BCA, B.Tech, MCA, and IT students preparing for DBMS or Java lab exams.

📚 Problem Statement 10: Library Database with Triggers

Tables:

Books (AccNo, Title, Author, Publisher, Count)

Library_Audit (AccNo, Title, Author, Publisher, Count)

🔹 Tasks Covered

Create Library Database

CREATE DATABASE LibraryDB;
USE LibraryDB;


Create Books Table

CREATE TABLE Books (
AccNo INT PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(100),
Publisher VARCHAR(100),
Count INT
);


Create Library_Audit Table

CREATE TABLE Library_Audit (
AccNo INT,
Title VARCHAR(100),
Author VARCHAR(100),
Publisher VARCHAR(100),
Count INT,
Action_Taken VARCHAR(50),
Action_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


BEFORE DELETE Trigger

Trigger inserts deleted records from Books into Library_Audit before deletion.

CREATE TRIGGER before_books_delete
BEFORE DELETE ON Books
FOR EACH ROW
INSERT INTO Library_Audit
(AccNo, Title, Author, Publisher, Count, Action_Taken)
VALUES (OLD.AccNo, OLD.Title, OLD.Author, OLD.Publisher, OLD.Count, 'Deleted');


AFTER UPDATE Trigger

Trigger inserts old and updated data into Library_Audit after update.

CREATE TRIGGER after_books_update
AFTER UPDATE ON Books
FOR EACH ROW
INSERT INTO Library_Audit
(AccNo, Title, Author, Publisher, Count, Action_Taken)
VALUES (NEW.AccNo, NEW.Title, NEW.Author, NEW.Publisher, NEW.Count, 'Updated');


Verification Commands

DELETE FROM Books WHERE AccNo = 101;
UPDATE Books SET Count = 8 WHERE AccNo = 102;
SELECT * FROM Library_Audit;

🧠 Concepts Covered

BEFORE and AFTER triggers

OLD and NEW pseudo-records

Data change tracking

Real-time auditing in SQL

Library database example for triggers

💻 Problem Statement 11: MySQL Database Connectivity with Java (JDBC)

Goal:
Implement Java program to perform basic Database Navigation Operations — Add, Delete, Edit, and Fetch records using MySQL.

🔹 Steps & Code Overview

Load JDBC Driver

Class.forName("com.mysql.cj.jdbc.Driver");


Establish Connection

Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/LibraryDB", "root", "password");


Perform CRUD Operations

Add Record:

PreparedStatement ps = con.prepareStatement("INSERT INTO Books VALUES (?,?,?,?,?)");


Edit Record:

PreparedStatement ps = con.prepareStatement("UPDATE Books SET Count=? WHERE AccNo=?");


Delete Record:

PreparedStatement ps = con.prepareStatement("DELETE FROM Books WHERE AccNo=?");


Fetch Record:

ResultSet rs = stmt.executeQuery("SELECT * FROM Books");


Close Connection

con.close();

🧠 Concepts You’ll Learn

JDBC architecture (Driver, Connection, Statement, ResultSet)

Connecting Java with MySQL using Connector/J

Executing SQL queries in Java

CRUD operations (Create, Read, Update, Delete)

Handling exceptions and SQL errors

Real-world navigation operations in Java

🎯 Learning Outcomes

✔️ Understanding SQL Triggers and their types (BEFORE, AFTER)
✔️ Implementing audit mechanisms for database changes
✔️ Establishing MySQL–Java connectivity using JDBC
✔️ Performing CRUD operations via Java
✔️ Preparing for DBMS and Java practical exams

👨‍💻 Perfect For

Computer Engineering and IT students

BCA / MCA learners

SQL & Java beginners

Students doing mini-projects with MySQL and Java

🏷️ Topics Covered

SQL Triggers, BEFORE Trigger, AFTER Trigger, JDBC, MySQL, Java Database Connectivity, CRUD Operations, PreparedStatement, Library Management System, SQL Practical, DBMS Practical, Database Navigation

📂 Resources

MySQL Trigger Script

Java Source Code (Add, Delete, Edit, Fetch)

Database Setup Instructions

(Add your Google Drive or GitHub link here)

🔖 Hashtags

#SQLPractical #DBMS #Triggers #MySQL #Java #JDBC #DatabaseConnectivity #CRUD #SQLTutorial #LibraryDatabase #BeforeTrigger #AfterTrigger #DatabaseAudit #EngineeringPracticals #SQLForBeginners