Trigger and Stored Procedure

Trigger and Stored Procedure

 Tables Setup

CREATE TABLE Department (
    D_id INT PRIMARY KEY,
    D_name VARCHAR(50)
);


CREATE TABLE Student (
    S_id INT PRIMARY KEY,
    S_name VARCHAR(50),
    D_id INT,
    FOREIGN KEY (D_id) REFERENCES Department(D_id)
);


CREATE TABLE Subject (
    Sub_id INT PRIMARY KEY,
    Sub_name VARCHAR(50)
);


CREATE TABLE Marks (
    S_id INT,
    Sub_id INT,
    Marks INT,
    FOREIGN KEY (S_id) REFERENCES Student(S_id),
    FOREIGN KEY (Sub_id) REFERENCES Subject(Sub_id)
);


Stored Procedure Example

👉 Example Task: Find average marks of a student.

DELIMITER $$

CREATE PROCEDURE Get_AvgMarks (IN student_id INT)
BEGIN
    SELECT S.S_name, AVG(M.Marks) AS Average_Marks
    FROM Marks M
    JOIN Student S ON M.S_id = S.S_id
    WHERE M.S_id = student_id
    GROUP BY S.S_name;
END$$

DELIMITER ;

 Usage:

CALL Get_AvgMarks(1);

 Trigger Example

Example Task: If marks are inserted more than 100, automatically correct them to 100.

DELIMITER $$

CREATE TRIGGER Check_Marks
BEFORE INSERT ON Marks
FOR EACH ROW
BEGIN
    IF NEW.Marks > 100 THEN
        SET NEW.Marks = 100;
    END IF;
END$$

DELIMITER ;

 Usage:

INSERT INTO Marks VALUES (1, 101, 120);

Example: 

Requirement

  • When a new student is inserted into the Student table,

  • Automatically insert that student’s S_id into the Marks table for all subjects,

  • With Marks = NULL initially.


DELIMITER $$

CREATE TRIGGER After_Student_Insert
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
    INSERT INTO Marks (S_id, Sub_id, Marks)
    SELECT NEW.S_id, Sub_id, NULL
    FROM Subject;
END$$

DELIMITER ;




Comments

Post a Comment

Popular posts from this blog

Data Control Language

 Data Control Language (DCL) 🔹 Step 1: Login as Admin (root) Open MySQL command line and login as root (or another admin account): mysql -u root -p Enter your root password. 🔹 Step 2: Create Database and Table CREATE DATABASE UniversityDB; USE UniversityDB; CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR ( 50 ), Marks INT ); INSERT INTO Students VALUES ( 1 , 'Amit' , 85 ), ( 2 , 'Raj' , 70 ), ( 3 , 'Neha' , 92 ), ( 4 , 'Sneha' , 60 ); 🔹   Step 3: Create Users CREATE USER 'user1' @ 'localhost' IDENTIFIED BY 'pass1' ; CREATE USER 'user2' @ 'localhost' IDENTIFIED BY 'pass2' ; CREATE USER 'user3' @ 'localhost' IDENTIFIED BY 'pass3' ; 'localhost' means these users can log in only from the same computer where MySQL is running. If you want t...
Read more

Checkpoint

Image
  What is a Checkpoint in DBMS? A checkpoint in a Database Management System (DBMS) is a mechanism used to minimize the amount of work needed for recovery after a system crash. It essentially acts like a “save point” — the system writes all modified data (dirty pages) from the buffer (RAM) to the disk and records a checkpoint in the log file . Purpose of Checkpoint Reduce recovery time — only redo or undo transactions after the last checkpoint. Ensure consistency between database and log files. Flush dirty pages (modified data not yet written to disk). Mark a safe state from which the system can recover. How It Works  When a checkpoint occurs: All committed transactions before the checkpoint are written to disk . The checkpoint record is written to the log file . Transactions after the checkpoint may still be in progress (these are handled by recovery if crash occurs). During recovery: Start from the last checkpoint , not from the b...
Read more

SUB Queries

 SUB Queries in SQL Table 1:  Students student_id name age department 1 Asha 19 Computer 2 Ravi 20 Mechanical 3 Priya 21 Computer 4 Suresh 20 Civil 5 Meena 19 Computer Table 2: Marks mark_id student_id subject marks 1 1 DBMS 85 2 1 Networks 78 3 2 DBMS 69 4 2 Networks 75 5 3 DBMS 92 6 3 Networks 88 7 4 DBMS 55 8 4 Networks 60 9 5 DBMS 70 10 5 Networks 72 CREATE TABLE Students (     student_id INT PRIMARY KEY,     name VARCHAR(50) NOT NULL,     age INT,     department VARCHAR(50) ); CREATE TABLE Marks (     mark_id INT PRIMARY KEY,     student_id INT,     subject VARCHAR(50),     marks INT,     FOREIGN KEY (student_id) REFERENCES Students(student_id) ); INSERT INTO Students (student_id, name, age, department) VALUES (1, 'Asha', 19, 'Computer'), (2, 'Ravi', 20, 'Mechanical'), (3, 'P...
Read more