This trigger uses predicates to log changes to the myStudent table.
SQL>
SQL> CREATE TABLE myLogTable (
2 change_type CHAR(1) NOT NULL,
3 changed_by VARCHAR2(8) NOT NULL,
4 timestamp DATE NOT NULL,
5 old_student_id NUMBER(5),
6 old_department CHAR(3),
7 old_course NUMBER(3),
8 old_grade CHAR(1),
9 new_student_id NUMBER(5),
10 new_department CHAR(3),
11 new_course NUMBER(3),
12 new_grade CHAR(1)
13 );
Table created.
SQL>
SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER myTrigger
2 BEFORE INSERT OR DELETE OR UPDATE ON myStudent
3 FOR EACH ROW
4 DECLARE
5 v_ChangeType CHAR(1);
6 BEGIN
7 IF INSERTING THEN
8 v_ChangeType := 'I';
9 ELSIF UPDATING THEN
10 v_ChangeType := 'U';
11 ELSE
12 v_ChangeType := 'D';
13 END IF;
14
15 INSERT INTO myLogTable
16 (change_type, changed_by, timestamp,
17 old_student_id, old_department, old_course, old_grade,
18 new_student_id, new_department, new_course, new_grade)
19 VALUES
20 (v_ChangeType, USER, SYSDATE,
21 :old.student_id, :old.department, :old.course, :old.grade,
22 :new.student_id, :new.department, :new.course, :new.grade);
23 END myTrigger;
24 /
Trigger created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, 'CS', 102, 'A');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, 'CS', 102, 'B');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, 'CS', 102, 'C');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, 'HIS', 101, 'A');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, 'HIS', 101, 'B');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, 'HIS', 101, 'B');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, 'HIS', 101, 'A');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10004, 'HIS', 101, 'C');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10005, 'HIS', 101, 'C');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, 'HIS', 101, 'E');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10007, 'HIS', 101, 'B');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, 'HIS', 101, 'A');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, 'HIS', 101, 'D');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, 'HIS', 101, 'A');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, 'NUT', 307, 'A');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, 'NUT', 307, 'A');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, 'MUS', 410, 'B');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, 'MUS', 410, 'E');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10011, 'MUS', 410, 'B');
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, 'MUS', 410, 'B');
1 row created.
SQL>
SQL> select * from myLogTable;
C CHANGED_ TIMESTAMP OLD_STUDENT_ID OLD OLD_COURSE O NEW_STUDENT_ID NEW NEW_COURSE N
- -------- --------- -------------- --- ---------- - -------------- --- ---------- -
I JAVA2S 18-JUN-08 10000 CS 102 A
I JAVA2S 18-JUN-08 10002 CS 102 B
I JAVA2S 18-JUN-08 10003 CS 102 C
I JAVA2S 18-JUN-08 10000 HIS 101 A
I JAVA2S 18-JUN-08 10001 HIS 101 B
I JAVA2S 18-JUN-08 10002 HIS 101 B
I JAVA2S 18-JUN-08 10003 HIS 101 A
I JAVA2S 18-JUN-08 10004 HIS 101 C
I JAVA2S 18-JUN-08 10005 HIS 101 C
I JAVA2S 18-JUN-08 10006 HIS 101 E
I JAVA2S 18-JUN-08 10007 HIS 101 B
C CHANGED_ TIMESTAMP OLD_STUDENT_ID OLD OLD_COURSE O NEW_STUDENT_ID NEW NEW_COURSE N
- -------- --------- -------------- --- ---------- - -------------- --- ---------- -
I JAVA2S 18-JUN-08 10008 HIS 101 A
I JAVA2S 18-JUN-08 10009 HIS 101 D
I JAVA2S 18-JUN-08 10010 HIS 101 A
I JAVA2S 18-JUN-08 10008 NUT 307 A
I JAVA2S 18-JUN-08 10010 NUT 307 A
I JAVA2S 18-JUN-08 10009 MUS 410 B
I JAVA2S 18-JUN-08 10006 MUS 410 E
I JAVA2S 18-JUN-08 10011 MUS 410 B
I JAVA2S 18-JUN-08 10000 MUS 410 B
20 rows selected.
SQL>
SQL> select * from myStudent;
STUDENT_ID DEP COURSE G
---------- --- ---------- -
10000 CS 102 A
10002 CS 102 B
10003 CS 102 C
10000 HIS 101 A
10001 HIS 101 B
10002 HIS 101 B
10003 HIS 101 A
10004 HIS 101 C
10005 HIS 101 C
10006 HIS 101 E
10007 HIS 101 B
STUDENT_ID DEP COURSE G
---------- --- ---------- -
10008 HIS 101 A
10009 HIS 101 D
10010 HIS 101 A
10008 NUT 307 A
10010 NUT 307 A
10009 MUS 410 B
10006 MUS 410 E
10011 MUS 410 B
10000 MUS 410 B
20 rows selected.
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL> drop table myStudent;
Table dropped.
SQL>
SQL>
Related examples in the same category