Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.

   
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>

Related examples in the same category

1.	create or replace trigger
2.	Oracle's syntax for creating a trigger based on two tables
3.	Trigger on each row
4.	Use Sequence in a trigger
5.	Empty trigger(before insert or update or delete)
6.	Create tigger on wrapper table
7.	This trigger sends messages over a pipe to record inserts into myStudent.
8.	Cascade inserts into myStudent into session and lecturer.
9.	Trigger Which Modifies a Mutating Table
10.	Creating a Trigger with cursor inside
11.	Autonumbering Trigger
12.	Use RAISE_APPLICATION_ERROR in a trigger
13.	Show errors for a trigger
14.	Submit job from a trigger
15.	Use sysdate and user function in a trigger

Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back. : Create Trigger « Trigger « Oracle PL / SQL

Related examples in the same category