Assignment
Mar24/ DBT/128
Database Technologies
Diploma in Advance Computing
March 2024
Trigger
1. Write a trigger (named insertStudent) that saves the message "Record inserted successfully" in
LOG(current date, current time, and message columns) table as soon as you insert the record in
STUDENT table.
drop trigger if exists insertStudent;
DELIMITER $
CREATE trigger insertStudent after INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO LOG (curdate, curtime, message) VALUES (CURDATE(), CURTIME(), 'Record
inserted successfully');
END $
DELIMITER ;
2. Write a trigger (named insertDuplicate) on STUDENT table, that as when we INSERT a record in
STUDENT table the same record should get duplicated (INSERTED) in STUDENT_LOG table. (Create
STUDENT_LOG table, having the same structure as STUDENT table).
drop trigger if exists insertDuplicate;
DELIMITER $
CREATE trigger insertDuplicate after INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO s_log values (new.id, new.namefirst, new.namelast, new.dob, new.emailID);
END $
DELIMITER ;
3. Write a trigger(named updateStudent) on STUDENT table, that as soon as we UPDATE student
emailID column data in STUDENT table, the update record should get inserted in STUDENT_LOG
table.
drop trigger if exists updateStudent;
DELIMITER $
CREATE trigger updateStudent after UPDATE ON student FOR EACH ROW
BEGIN
INSERT INTO s_log values (old.id, old.namefirst, old.namelast, old.dob, old.emailID);
END $
Infoway Technologies, 3rd Floor Commerce Centre, Rambaug Colony, Paud Road Pune 411038
DELIMITER ;
4. Write a trigger (named deleteStudent) on STUDENT table, that as soon as we DELETE any record
from STUDENT table, then that record should get inserted into STUDENT_LOG table.
drop trigger if exists deleteStudent;
DELIMITER $
CREATE trigger deleteStudent after DELETE ON student FOR EACH ROW
BEGIN
INSERT INTO s_log values (old.id, old.namefirst, old.namelast, old.dob, old.emailID);
END $
DELIMITER ;
5. Write a trigger (named insertValidation) on STUDENT table, that if today is Sunday then, no
record should get inserted in STUDENT table.
drop trigger if exists insertValidation;
DELIMITER $
CREATE trigger insertValidation before INSERT ON student FOR EACH ROW
BEGIN
if dayofweek(curdate()) = 1 then
signal sqlstate '42000' set message_text = 'no record';
end if;
END $
DELIMITER ;
Infoway Technologies, 3rd Floor Commerce Centre, Rambaug Colony, Paud Road Pune 411038
