Mega Code Archive
Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back
<![CDATA[
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> 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> CREATE OR REPLACE TRIGGER myTrigger
2 BEFORE INSERT OR DELETE OR UPDATE ON myStudent
3 FOR EACH ROW
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 v_ChangeType CHAR(1);
7 BEGIN
8 IF INSERTING THEN
9 v_ChangeType := 'I';
10 ELSIF UPDATING THEN
11 v_ChangeType := 'U';
12 ELSE
13 v_ChangeType := 'D';
14 END IF;
15
16 INSERT INTO myLogTable
17 (change_type, changed_by, timestamp,
18 old_student_id, old_department, old_course, old_grade,
19 new_student_id, new_department, new_course, new_grade)
20 VALUES
21 (v_ChangeType, USER, SYSDATE,
22 :old.student_id, :old.department, :old.course, :old.grade,
23 :new.student_id, :new.department, :new.course, :new.grade);
24 COMMIT;
25 END myTrigger;
26 /
Trigger created.
SQL>
SQL> drop table myStudent;
Table dropped.
SQL> drop table myLogTable;
Table dropped.
SQL>
]]>