Scribd
Upload
9 views5 pages

DBMS Lab Week 10

The document outlines the creation of MySQL triggers for various scenarios, including backing up deleted records from a students table, updating a timestamp in an orders table upon record updates, and preventing the insertion of students under the age of 18. It provides SQL queries for setting up these triggers and demonstrates their functionality. Additionally, it includes commands to display and delete triggers in the database.

Uploaded by

prabhat.vl.23
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views5 pages

DBMS Lab Week 10

The document outlines the creation of MySQL triggers for various scenarios, including backing up deleted records from a students table, updating a timestamp in an orders table upon record updates, and preventing the insertion of students under the age of 18. It provides SQL queries for setting up these triggers and demonstrates their functionality. Additionally, it includes commands to display and delete triggers in the database.

Uploaded by

prabhat.vl.23
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Week 10

Problem 1. Write a MySQL trigger that automatically inserts deleted records from a students table into
a students backup table before they are deleted

Query:

use db1;

CREATE TABLE students (


roll_no INT PRIMARY KEY,
name VARCHAR(50),
marks INT
);

INSERT INTO students (roll_no, name, marks) VALUES


(1, 'Aarav', 95),
(2, 'Bhavya', 82),
(3, 'Charvi', 45),
(4, 'Dhruv', 37),
(5, 'Esha', 76),
(6, 'Farhan', 9),
(7, 'Gauri', 62),
(8, 'Harsh', 5),
(9, 'Ishita', 88),
(10, 'Jay', 23);

CREATE TABLE backup_students (


roll_no INT PRIMARY KEY,
name VARCHAR(50),
marks INT
);

delimiter $$
CREATE TRIGGER del_above_7
BEFORE DELETE
ON students
FOR EACH ROW
BEGIN
insert into backup_students(roll_no, name, marks) values(old.roll_no, [Link], [Link]);
END$$
delimiter ;

delete from students where roll_no >7;


select * from backup_students;

1|Page
Output:

Problem 2. Create a trigger that updates the last updated column of a table orders whenever any record is
updated.
Query:

CREATE TABLE orders (


order_id INT PRIMARY KEY,
customer_name VARCHAR(100),
amount DECIMAL(10,2),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders (order_id, customer_name, amount) VALUES


(1, 'Aarav Enterprises', 1500.00),
(2, 'Bhavya Traders', 899.99),
(3, 'Charvi Pvt Ltd', 1200.50),
(4, 'Dhruv Retail', 499.00),
(5, 'Esha & Co.', 10250.75);

DELIMITER $$

CREATE TRIGGER trg_update_last_updated


BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
SET NEW.last_updated = CURRENT_TIMESTAMP;
END$$

DELIMITER ;

update orders set amount = 1000 where order_id = 1;


select * from orders;

2|Page
Output:

Note: timestamp updated (can crosscheck from table and time in my laptop)

Problem 3. Create a BEFORE INSERT trigger on a students table that prevents inserting a student with
age less than 18. If age is less than 18, throw an error.

Query:

ALTER TABLE students ADD COLUMN age INT;

DELIMITER $$
CREATE TRIGGER trg_block_minor_students
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
IF [Link] < 18 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Student age must be at least 18';
END IF;
END$$

DELIMITER ;

insert into students values(11, 'Akash Garg', 100, 17);

Output:

Problem 4. Write SQL to display all triggers present in a database and describe how you would delete a
3|Page
Trigger.
Query:

SHOW TRIGGERS FROM db1;


DROP TRIGGER IF EXISTS del_above_7;

Output:

4|Page
5|Page

You might also like