-
Notifications
You must be signed in to change notification settings - Fork 14
Expand file tree
/
Copy pathautontrigger3.sql
More file actions
102 lines (82 loc) · 2.31 KB
/
Copy pathautontrigger3.sql
File metadata and controls
102 lines (82 loc) · 2.31 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
96
97
98
99
100
101
/*
|| AUtonomous transactions in database triggers.
|| This version shows that a RAISE in the
|| AUTONOMOUS_TRANSACTION procedure will cause an
|| automatic rollback of the DML. Makes sense; it's
|| the top-level block in the transaction.
*/
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
);
IF audit_ceo_comp.description LIKE 'AFTER%'
THEN
RAISE VALUE_ERROR;
END IF;
COMMIT;
END;
/
CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
BEFORE INSERT ON ceo_compensation FOR EACH ROW
DECLARE
ok BOOLEAN := TRUE;
BEGIN
audit_ceo_comp (
:new.name, 'BEFORE INSERT', SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
AFTER INSERT ON ceo_compensation FOR EACH ROW
DECLARE
ok BOOLEAN := FALSE;
BEGIN
audit_ceo_comp (
:new.name, 'AFTER INSERT', SYSDATE);
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/
*/