Date of Practical: Practical 12 Date of Submission:
AIM
To implement procedure and triggers in SQL with two examples each on database Project
assigned to each student, by running on WAMP/ LAMP /XAMPP /SQL server.
PROCEDURE
Implementing Procedures on Project Database
1. Procedure: Insert BOOKS_INFO - This procedure inserts a new record into
books_info1 table.
SQL QUERY :
DELIMITER $$
CREATE PROCEDURE AddBook (
IN p_SUBJECT VARCHAR(255),
IN p_TITLE VARCHAR(255),
IN p_SEMESTER INT,
IN p_AUTHOR VARCHAR(255),
IN p_PUBLISHER VARCHAR(255),
IN p_EDITION VARCHAR(50)
)
BEGIN
INSERT INTO books_info1 (SUBJECT, TITLE, SEMESTER, AUTHOR,
PUBLISHER, EDITION)
VALUES (p_SUBJECT, p_TITLE, p_SEMESTER, p_AUTHOR, p_PUBLISHER,
p_EDITION);
END$$
DELIMITER ;
CO23317 Page no |
Date of Practical: Practical 12 Date of Submission:
TEST QUERIES:
Add a new book related to academics:
1. CALL AddBook('Mathematics', 'Calculus I', 1, 'James Stewart', 'Cengage', '8th Edition');
2. CALL AddBook('Physics', 'Quantum Mechanics', 3, 'David Griffiths', 'Pearson', '2nd Edition');
2. Procedure: Add faculty Record - This procedure adds a faculty record to
the faculty_info table.
SQL QUERY:
DELIMITER $$
CREATE PROCEDURE AddFaculty (
IN p_name VARCHAR(255),
IN p_designation VARCHAR(255),
IN p_specialization VARCHAR(255),
IN p_subject VARCHAR(255),
IN p_room_no VARCHAR(50),
IN p_contact_no VARCHAR(20),
IN p_email_id VARCHAR(255)
)
BEGIN
INSERT INTO FACULTY_INFO (NAME, DESIGNATION, SPECIALIZATION, SUBJECT,
ROOM_NO, CONTACT_NO, EMAIL_ID)
VALUES (p_name, p_designation, p_specialization, p_subject, p_room_no, p_contact_no,
p_email_id);
END$$
DELIMITER ;
CO23317 Page no |
Date of Practical: Practical 12 Date of Submission:
TEST QUERIES
Add a record for a faculty
CALL AddFaculty(
'Dr. Jane Smith',
'Assistant Professor',
'Data Science',
'Big Data',
'0',
'9876543210',
'[email protected]'
);
CO23317 Page no |
Date of Practical: Practical 12 Date of Submission:
Implementing Triggers on Project Database
3. Trigger: Ensure Valid Mobile Numbers in faculty_info table- This trigger
ensures that mobile numbers in the faculty table are exactly 10 digits long.
SQL Query :
DELIMITER $$
CREATE TRIGGER BeforeFacultyInsert
BEFORE INSERT ON FACULTY_INFO
FOR EACH ROW
BEGIN
-- Check if the contact number is exactly 10 digits
IF LENGTH(NEW.CONTACT_NO) != 10 OR NOT
NEW.CONTACT_NO REGEXP '^[0-9]{10}$' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR: Mobile number
must be 10 digits long';
END IF;
END$$
DELIMITER ;
TEST QUERIES
Attempt to insert a record with an invalid mobile number (this should fail)
INSERT INTO FACULTY_INFO (NAME, DESIGNATION,
SPECIALIZATION, SUBJECTS, ROOM_NO, CONTACT_NO,
EMAIL_ID)
VALUES ('Dr. John Doe', 'Professor', 'Physics', 'Quantum Mechanics',
'R101', '12345', '
[email protected]');
CO23317 Page no |
Date of Practical: Practical 12 Date of Submission:
4. Trigger: trigger that ensures each email ID is unique in the FACULTY_INFO
table before inserting a record,
SQL QUERY-
DELIMITER $$
CREATE TRIGGER BeforeFacultyInsertEmailCheck
BEFORE INSERT ON FACULTY_INFO
FOR EACH ROW
BEGIN
IF EXISTS (SELECT 1 FROM FACULTY_INFO WHERE EMAIL_ID = NEW.EMAIL_ID) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR: Duplicate email ID is not allowed';
END IF;
END$$
DELIMITER ;
TEST QUERIES:
-Attempt to insert a invalid record
INSERT INTO FACULTY_INFO (NAME, DESIGNATION, SPECIALIZATION, SUBJECTS, ROOM_NO,
CONTACT_NO, EMAIL_ID)
VALUES ('John Doe', 'Assistant Professor', 'Physics', 'Quantum Mechanics', 'R101', '1234567894',
'
[email protected]');
CO23317 Page no |
