DBMS 8

CREATE TABLE Library (BookID INT PRIMARY KEY, BookName VARCHAR(50), Edition INT, Quantity INT);

CREATE TABLE LibraryAudit (BookID INT PRIMARY KEY, BookName VARCHAR(50), Edition INT, Quantity INT, ModDate TIMESTAMP, OperType VARCHAR(30), ModUserDetails VARCHAR(100));

INSERT INTO Library VALUES (001, 'Ramayana', 12,11);
INSERT INTO Library VALUES (012, 'Shoe Dog', 2, 4);
INSERT INTO Library VALUES (034, 'Daredevil', 4, 4);
INSERT INTO Library VALUES (101, 'Wings of Fire', 9, 12);

SELECT * FROM Library;
-- +--------+---------------+---------+----------+
-- | BookID | BookName      | Edition | Quantity |
-- +--------+---------------+---------+----------+
-- |      1 | Ramayana      |      12 |       10 |
-- |     12 | Shoe Dog      |       2 |        4 |
-- |     34 | Daredevil     |       4 |        4 |
-- |    101 | Wings of Fire |       9 |       12 |
-- +--------+---------------+---------+----------+

DELIMITER $

CREATE TRIGGER T1
AFTER UPDATE ON Library
FOR EACH ROW
BEGIN
  INSERT INTO LibraryAudit VALUES (OLD.BookID, OLD.BookName, OLD.Edition, OLD.Quantity, CURRENT_TIMESTAMP, "UPDATE", CURRENT_USER());
END $

CREATE TRIGGER T2
AFTER DELETE ON Library
FOR EACH ROW
BEGIN
  INSERT INTO LibraryAudit VALUES (OLD.BookID, OLD.BookName, OLD.Edition, OLD.Quantity, CURRENT_TIMESTAMP, "DELETE", CURRENT_USER());
END $

DELIMITER ;

UPDATE Library SET Quantity=10 WHERE BookID=001;
UPDATE Library SET Edition=3 WHERE BookID=012;
UPDATE Library SET Quantity=8 WHERE BookID=101;

DELETE FROM Library WHERE BookID=034; 

SELECT * FROM LibraryAudit;
-- +--------+---------------+---------+----------+---------------------+----------+----------------+
-- | BookID | BookName      | Edition | Quantity | ModDate             | OperType | ModUserDetails |
-- +--------+---------------+---------+----------+---------------------+----------+----------------+
-- |      1 | Ramayana      |      12 |       11 | 2022-09-15 09:07:34 | UPDATE   | te31141A@%     |
-- |     12 | Shoe Dog      |       2 |        4 | 2022-09-15 09:14:43 | UPDATE   | te31141A@%     |
-- |     34 | Daredevil     |       4 |        4 | 2022-09-15 09:15:00 | DELETE   | te31141A@%     |
-- |    101 | Wings of Fire |       9 |       12 | 2022-09-15 09:13:50 | UPDATE   | te31141A@%     |
-- +--------+---------------+---------+----------+---------------------+----------+----------------+