-
Notifications
You must be signed in to change notification settings - Fork 3
/
Assignment_7.sql
74 lines (69 loc) · 2.95 KB
/
Assignment_7.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
/*
CREATE TABLE lib_tab(book_name VARCHAR2(25),status VARCHAR2(15));
CREATE TABLE library_audit(date_modified DATE, book_name VARCHAR2(25),old_status VARCHAR(15),new_status VARCHAR2(15),action VARCHAR2(25));
*/
CREATE OR REPLACE TRIGGER trigger_1
AFTER UPDATE OR DELETE OR INSERT ON lib_tab FOR EACH ROW
ENABLE
BEGIN
IF UPDATING THEN
dbms_output.put_line(:OLD.status);
INSERT INTO library_audit VALUES (SYSDATE,:OLD.book_name,:OLD.status,:NEW.status,'UPDATE');
ELSIF INSERTING THEN
dbms_output.put_line(:NEW.status);
INSERT INTO library_audit VALUES (SYSDATE,:NEW.book_name,:OLD.status,:NEW.status,'INSERT');
ELSE
dbms_output.put_line(:OLD.book_name||'deleting');
INSERT INTO library_audit VALUES(SYSDATE,:OLD.book_name,:OLD.status,:NEW.status,'DELETE');
END IF;
END;
/
/*
DELETE FROM lib_tab WHERE book_name = 'SILENT HILL';
UPDATE lib_tab SET status = 'UNAVAILABLE' WHERE book_name = 'UNCHARTED';
UPDATE lib_tab SET status = 'PRE-ORDER' WHERE book_name = 'GOD OF WAR';
UPDATE lib_tab SET status = 'AVAILABLE' WHERE book_name = 'UNCHARTED';
INSERT INTO lib_tab VALUES('SPM','UNAVAILABLE');
Select * from library_audit;
Select * from lib_tab;
*/
/*
INSERT INTO lib_tab VALUES('DARK MATTER','AVAILABLE');
INSERT INTO lib_tab VALUES('SILENT HILL','UNAVAILABLE');
INSERT INTO lib_tab VALUES('GOD OF WAR','AVAILABLE');
INSERT INTO lib_tab VALUES('SPIDER-MAN','UNAVAILABLE');
INSERT INTO lib_tab VALUES('UNCHARTED','AVAILABLE');
*/
/*
ORIGINAL lib_tab :
BOOK_NAME STATUS
------------------------- ---------------
DARK MATTER AVAILABLE
SILENT HILL UNAVAILABLE
GOD OF WAR AVAILABLE
SPIDER-MAN UNAVAILABLE
UNCHARTED AVAILABLE
/*
/*
DELETE FROM lib_tab WHERE book_name = 'SILENT HILL';
UPDATE lib_tab SET status = 'UNAVAILABLE' WHERE book_name = 'UNCHARTED';
UPDATE lib_tab SET status = 'PRE-ORDER' WHERE book_name = 'GOD OF WAR';
*/
/*
OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
DATE_MODIFIED BOOK_NAME OLD_STATUS NEW_STATUS ACTION
-------------- ------------ ---------- ----------- ----------
16-09-22 SILENT HILL UNAVAILABLE DELETE
16-09-22 UNCHARTED AVAILABLE UNAVAILABLE UPDATE
16-09-22 GOD OF WAR AVAILABLE PRE-ORDER UPDATE
/*
/*
FINAL lib_tab :--------------------------------------------------------------------------------------------------------------------------------------------------------------------
BOOK_NAME STATUS
------------------------- ---------------
DARK MATTER AVAILABLE
GOD OF WAR PRE-ORDER
SPIDER-MAN UNAVAILABLE
UNCHARTED UNAVAILABLE
*/