-
Notifications
You must be signed in to change notification settings - Fork 14
Expand file tree
/
Copy pathautontrigger2.sql
More file actions
96 lines (79 loc) · 2.07 KB
/
Copy pathautontrigger2.sql
File metadata and controls
96 lines (79 loc) · 2.07 KB
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
DROP TABLE ceo_compensation;
CREATE TABLE ceo_compensation (
company VARCHAR2(100),
name VARCHAR2(100),
compensation NUMBER,
layoffs NUMBER);
DROP TABLE ceo_comp_history;
CREATE TABLE ceo_comp_history (
name VARCHAR2(100),
description VARCHAR2(255),
occurred_on DATE);
CREATE OR REPLACE PROCEDURE audit_ceo_comp (
name IN VARCHAR2,
description IN VARCHAR2,
occurred_on IN DATE
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ceo_comp_history VALUES (
audit_ceo_comp.name,
audit_ceo_comp.description,
audit_ceo_comp.occurred_on
);
COMMIT;
END;
/
CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
BEFORE INSERT ON ceo_compensation FOR EACH ROW
DECLARE
ok BOOLEAN := TRUE;
BEGIN
IF ok
THEN
audit_ceo_comp (
:new.name, 'BEFORE INSERT', SYSDATE);
END IF;
END;
/
CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
AFTER INSERT ON ceo_compensation FOR EACH ROW
DECLARE
ok BOOLEAN := FALSE;
BEGIN
IF ok
THEN
audit_ceo_comp (
:new.name, 'AFTER INSERT', SYSDATE);
ELSE
RAISE VALUE_ERROR;
END IF;
END;
/
COLUMN name FORMAT a20
COLUMN description FORMAT a30
SELECT name,
description,
TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
FROM ceo_comp_history;
BEGIN
INSERT INTO ceo_compensation VALUES (
'Mattel', 'Jill Barad', 9100000, 2700);
INSERT INTO ceo_compensation VALUES (
'American Express Company', 'Harvey Golub', 33200000, 3300);
INSERT INTO ceo_compensation VALUES (
'Eastman Kodak', 'George Fisher', 10700000, 20100);
ROLLBACK; -- I wish!
END;
/
SELECT name,
description,
TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
FROM ceo_comp_history;
/*======================================================================
| Supplement to the fifth edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc.
| To submit corrections or find more code samples visit
| http://oreilly.com/catalog/9780596514464/
*/