CREATE TABLE Library (
book_id NUMBER PRIMARY KEY,
book_name VARCHAR2(100),
book_author VARCHAR2(100)
);

CREATE TABLE Library_Audit (
event_time TIMESTAMP,
book_id NUMBER PRIMARY KEY,
book_name VARCHAR2(100),
book_author VARCHAR2(100),
remarks VARCHAR2(500)
);

INSERT INTO Library VALUES (001, 'Computer Architecture (7th ed.)', 'Patterson and Hennessey');
INSERT INTO Library VALUES (002, 'Introduction to Linear Algebra (2nd Ed.)', 'Gilbert Strang'); -- update to 5th edition
INSERT INTO Library VALUES (003, 'Automata Theory (1st Ed.)', 'Ulman'); -- update to 2nd edition
INSERT INTO Library VALUES (004, 'Operating System Concepts (8th Ed.)', 'Silberschatz');
INSERT INTO Library VALUES (005, '1984', 'George Orwell'); -- delete book
INSERT INTO Library VALUES (006, 'Digital Logic (3rd Ed.)', 'Morris Mano'); -- update to 6th edition
INSERT INTO Library VALUES (007, 'Alice in Wonderland', 'Lewis Caroll'); -- delete book
INSERT INTO Library VALUES (008, 'Database System Concepts (7th Ed.)', 'Korth');

CREATE OR REPLACE TRIGGER updateTrigger AFTER UPDATE
ON Library FOR EACH ROW
BEGIN
INSERT INTO Library_Audit VALUES (
SYSTIMESTAMP,
:OLD.book_id,
:OLD.book_name,
:OLD.book_author,
'Updated'
);
END;

CREATE OR REPLACE TRIGGER deleteTrigger AFTER DELETE
On Library FOR EACH ROW
BEGIN
INSERT INTO Library_Audit VALUES (
SYSTIMESTAMP,
:OLD.book_id,
:OLD.book_name,
:OLD.book_author,
'Removed'
);
END;

DELETE FROM Library WHERE book_id = 005;

UPDATE Library
SET book_name = 'Introduction to Linear Algebra (5th Ed.)'
WHERE book_id = 002;

UPDATE Library
SET book_name = 'Automata Theory (2nd Ed.)'
WHERE book_id = 003;

DELETE FROM Library WHERE book_id = 007;

UPDATE Library
SET book_name = 'Digital Logic (6th Ed.)'
WHERE book_id = 006;