Zeal College of Engineering and Research
Subject: Database Management System Lab
Name: Janhavi Rahul Raikar
Roll No: T213011
Div: C
Batch: C1
Group A: Practical No. 8
PROBLEM STATEMENT:
Write a database trigger on Library table. The System should keep track of the records that
are being updated or deleted. The old value of updated or deleted records should be added in
Library Audit table.
CODE:
d-comp-dbms-17@dcompdbms17-OptiPlex-3070:~$ sudo mysql;
[sudo] password for d-comp-dbms-17:
Sorry, try again.
[sudo] password for d-comp-dbms-17:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.39-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use studcourse;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table l_brorrower(rollno int,name varchar(90),name_of_book varchar(90));
Query OK, 0 rows affected (0.64 sec)
mysql> create table l_audit(n_rollno int,n_name varchar(90),new_name_of_book varchar(90));
Query OK, 0 rows affected (0.55 sec)
mysql> desc l_borrower;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| rollno | int | YES | | NULL | |
| name | varchar(90) | YES | | NULL | |
| name_of_book | varchar(90) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc l_audit;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| n_rollno | int | YES | | NULL | |
| n_name | varchar(90) | YES | | NULL | |
| new_name_of_book | varchar(90) | YES | | NULL | |
+------------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into l_borrower values(1,"rushi","math"),(2,"imam","cns"),(3,"krishna","iot"),
(4,"janhavi","toc"),(5,"amit","Dbms");
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> delimiter //
mysql> create trigger tr_borrower
-> before insert on l_borrower
-> for each row
-> begin
-> insert into l_audit values(n_rollno,n_name,new_name_of_book);
-> end //
mysql> insert into l_borrower values(3,"krishna","iot"),(4,"amit","dbms");
-> //
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from l_audit;
-> //
+--------+---------+--------------+
| rollno | name | name_of_book |
+--------+---------+--------------+
| 3 | krishna | iot |
| 4 | amit | dbms |
+--------+---------+--------------+
2 rows in set (0.00 sec)
mysql> delimiter //
mysql> create trigger trd1_borrower
-> before delete on l_borrower
-> for each row
-> begin
-> insert into l_audit values(old.rollno,old.name,old.name_of_book);
-> end //
Query OK, 0 rows affected (0.14 sec)
mysql> delete from l_borrower where rollno=2;
-> //
Query OK, 1 row affected (0.09 sec)
mysql> select * from l_borrower;
-> //
+--------+---------+--------------+
| rollno | name | name_of_book |
+--------+---------+--------------+
| 1 | rushi | toc |
| 3 | krishna | iot |
| 4 | amit | dbms |
+--------+---------+--------------+
3 rows in set (0.00 sec)
mysql> select * from l_audit;
-> //
+--------+---------+--------------+
| rollno | name | name_of_book |
+--------+---------+--------------+
| 3 | krishna | iot |
| 4 | amit | dbms |
| 2 | imam | cns |
+--------+---------+--------------+
3 rows in set (0.00 sec)
mysql> delimiter //
mysql> create trigger tru1_borrower
-> before update on l_borrower
-> for each row
-> begin
-> insert into l_audit values(new.rollno,new.name,new.name_of_book);
-> end //
Query OK, 0 rows affected (0.14 sec)
mysql> update l_borrower set name="amit" where rollno=2;
-> //
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from l_audit;
-> //
+--------+---------+--------------+
| rollno | name | name_of_book |
+--------+---------+--------------+
| 3 | krishna | iot |
| 4 | amit | dbms |
| 2 | imam | cns |
| 2 | amit | cns |
+--------+---------+--------------+
7 rows in set (0.00 sec)
