DBMSL Ass 7A - Library Trigger

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;