· 8 years ago · Mar 05, 2018, 02:46 PM
1SET SERVEROUTPUT ON;
2SET ECHO OFF;
3
4
5CREATE TABLE logon_logs (
6 username VARCHAR2(10),
7 log_time TIMESTAMP
8);
9/
10
11
12CREATE OR REPLACE TRIGGER log_user_logon
13AFTER LOGON ON DATABASE
14BEGIN
15 INSERT INTO logon_logs VALUES(USER, CURRENT_TIMESTAMP);
16END;
17/
18
19
20CREATE TABLE STUDENTS (
21 ID NUMBER,
22 NAME VARCHAR2(10),
23 GROUP_ID NUMBER
24);
25/
26
27
28CREATE OR REPLACE TRIGGER unique_id
29AFTER INSERT OR UPDATE ON STUDENTS
30DECLARE
31 duplicates_count INTEGER := 0;
32BEGIN
33
34 SELECT count(*) INTO duplicates_count FROM STUDENTS INNER JOIN
35 (
36 SELECT ID, NAME
37 FROM STUDENTS
38 GROUP BY ID, NAME
39 HAVING count(*) > 1
40 );
41 IF duplicates_count > 0 THEN
42 RAISE_APPLICATION_ERROR(-20000, 'ID must be unique!');
43 END IF;
44END;
45/
46
47
48CREATE SEQUENCE students_id_seq START WITH 1;
49/
50
51
52CREATE OR REPLACE TRIGGER id_increment
53BEFORE INSERT ON STUDENTS
54FOR EACH ROW
55BEGIN
56 IF :NEW.ID IS NULL THEN
57 :NEW.id := students_id_seq.NEXTVAL;
58 END IF;
59END;
60/
61
62
63CREATE TABLE GROUPS (
64 ID NUMBER,
65 NAME VARCHAR2(10),
66 C_VAL NUMBER
67);
68/
69
70
71CREATE OR REPLACE TRIGGER student_group_fk
72BEFORE INSERT ON STUDENTS
73FOR EACH ROW
74DECLARE
75 group_id NUMBER;
76BEGIN
77 SELECT ID INTO group_id FROM GROUPS WHERE GROUPS.ID = :NEW.GROUP_ID;
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 RAISE_APPLICATION_ERROR(-20001, 'Group ' || to_char(:NEW.GROUP_ID) || ' do not exists');
81END;
82/
83
84
85CREATE OR REPLACE TRIGGER students_groups_cas_del
86BEFORE DELETE ON GROUPS
87FOR EACH ROW
88BEGIN
89 FOR student_id IN (SELECT STUDENTS.ID FROM STUDENTS WHERE STUDENTS.GROUP_ID = :OLD.ID)
90 LOOP
91 DELETE FROM STUDENTS WHERE STUDENTS.ID = student_id.ID;
92 END LOOP;
93END;
94/
95
96
97CREATE TABLE students_backup(
98 ID NUMBER,
99 STUDENT_ID NUMBER,
100 STUDENT_NAME VARCHAR2(10),
101 STUDENT_GROUP_ID NUMBER,
102 operation CHAR(1),
103 time_executed TIMESTAMP
104);
105/
106
107
108CREATE SEQUENCE students_backup_id_seq START WITH 1;
109/
110
111
112CREATE OR REPLACE TRIGGER students_bckp_id_increment
113BEFORE INSERT ON students_backup
114FOR EACH ROW
115BEGIN
116 IF :NEW.ID IS NULL THEN
117 :NEW.id := students_backup_id_seq.NEXTVAL;
118 END IF;
119END;
120/
121
122
123CREATE OR REPLACE TRIGGER students_backuping
124AFTER INSERT OR UPDATE OR DELETE ON STUDENTS
125FOR EACH ROW
126BEGIN
127 CASE
128 WHEN INSERTING THEN
129 INSERT INTO students_backup
130 VALUES (
131 NULL, :NEW.ID, :NEW.NAME, :NEW.GROUP_ID, '1', CURRENT_TIMESTAMP
132 );
133 WHEN UPDATING THEN
134 INSERT INTO students_backup
135 VALUES (
136 NULL, :OLD.ID, :OLD.NAME, :OLD.GROUP_ID, NULL, CURRENT_TIMESTAMP
137 );
138 WHEN DELETING THEN
139 INSERT INTO students_backup
140 VALUES (
141 NULL, :OLD.ID, :OLD.NAME, :OLD.GROUP_ID, '0', CURRENT_TIMESTAMP
142 );
143 END CASE;
144END;
145/
146
147
148CREATE OR REPLACE PROCEDURE rollback_students(time_limit IN TIMESTAMP) AS
149BEGIN
150 FOR sutudents_op IN (
151 SELECT * FROM students_backup
152 WHERE students_backup.time_executed >= time_limit
153 ORDER BY students_backup.ID DESC
154 )
155 LOOP
156 IF sutudents_op.OPERATION = '1' THEN
157 DELETE FROM STUDENTS WHERE STUDENTS.ID = sutudents_op.STUDENT_ID;
158 ELSIF sutudents_op.OPERATION = '0' THEN
159 INSERT INTO STUDENTS VALUES (sutudents_op.STUDENT_ID, sutudents_op.STUDENT_NAME, sutudents_op.STUDENT_GROUP_ID);
160 ELSIF sutudents_op.OPERATION IS NULL THEN
161 UPDATE STUDENTS
162 SET
163 STUDENTS.NAME = sutudents_op.STUDENT_NAME,
164 STUDENTS.GROUP_ID = sutudents_op.STUDENT_GROUP_ID
165 WHERE ID = sutudents_op.STUDENT_ID;
166 END IF;
167 END LOOP;
168END;
169/
170
171
172CREATE OR REPLACE TRIGGER goups_auto_cval
173AFTER INSERT OR UPDATE OR DELETE ON STUDENTS
174FOR EACH ROW
175DECLARE
176 cval NUMBER;
177 group_id NUMBER;
178BEGIN
179 CASE
180 WHEN INSERTING THEN
181 UPDATE GROUPS
182 SET GROUPS.C_VAL = GROUPS.C_VAL + 1
183 WHERE GROUPS.ID = :NEW.GROUP_ID;
184 WHEN UPDATING('GROUP_ID') THEN
185 UPDATE GROUPS
186 SET GROUPS.C_VAL = GROUPS.C_VAL - 1
187 WHERE GROUPS.ID = :OLD.GROUP_ID;
188 UPDATE GROUPS
189 SET GROUPS.C_VAL = GROUPS.C_VAL + 1
190 WHERE GROUPS.ID = :NEW.GROUP_ID;
191 WHEN DELETING THEN
192 UPDATE GROUPS
193 SET GROUPS.C_VAL = GROUPS.C_VAL - 1
194 WHERE GROUPS.ID = :OLD.GROUP_ID;
195 END CASE;
196END;
197/
198
199
200BEGIN
201 INSERT INTO GROUPS VALUES (1, 'Fools', 0);
202 INSERT INTO STUDENTS VALUES (NULL, 'Jack', 1);
203 INSERT INTO STUDENTS VALUES (NULL, 'Jack', 1);
204 UPDATE STUDENTS
205 SET STUDENTS.ID = 1
206 WHERE STUDENTS.ID = 2;
207 UPDATE STUDENTS
208 SET STUDENTS.NAME = 'Fool'
209 WHERE STUDENTS.ID = 2;
210 INSERT INTO STUDENTS VALUES (NULL, 'Jack', 2);
211 DELETE FROM GROUPS WHERE ID = 1;
212END;
213/
214
215
216BEGIN
217 rollback_students(TO_TIMESTAMP('15.02.17 22:22:13,784477000'));
218END;
219/