Mega Code Archive

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 RNTSOFT 18-JUN-08 10000 CS 102 A I RNTSOFT 18-JUN-08 10002 CS 102 B I RNTSOFT 18-JUN-08 10003 CS 102 C I RNTSOFT 18-JUN-08 10000 HIS 101 A I RNTSOFT 18-JUN-08 10001 HIS 101 B I RNTSOFT 18-JUN-08 10002 HIS 101 B I RNTSOFT 18-JUN-08 10003 HIS 101 A I RNTSOFT 18-JUN-08 10004 HIS 101 C I RNTSOFT 18-JUN-08 10005 HIS 101 C I RNTSOFT 18-JUN-08 10006 HIS 101 E I RNTSOFT 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 RNTSOFT 18-JUN-08 10008 HIS 101 A I RNTSOFT 18-JUN-08 10009 HIS 101 D I RNTSOFT 18-JUN-08 10010 HIS 101 A I RNTSOFT 18-JUN-08 10008 NUT 307 A I RNTSOFT 18-JUN-08 10010 NUT 307 A I RNTSOFT 18-JUN-08 10009 MUS 410 B I RNTSOFT 18-JUN-08 10006 MUS 410 E I RNTSOFT 18-JUN-08 10011 MUS 410 B I RNTSOFT 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>