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