Audit trigger : Auidt Table « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Auidt Table

SQL>
SQL> CREATE TABLE game_player
  2  (player_id    NUMBER,
  3   game_id      NUMBER,
  4   group_number NUMBER,
  5   marked       VARCHAR2(1) DEFAULT 'N',
  6   pcmac        VARCHAR2(1) DEFAULT 'N',
  7   score        NUMBER,
  8   CONSTRAINT game_player_pk
  9   PRIMARY KEY (player_id, game_id, group_number));

Table created.

SQL>
SQL> CREATE TABLE game_player_audit
  2  (player_id    NUMBER,
  3   game_id      NUMBER,
  4   group_number NUMBER,
  5   old_marked   VARCHAR2(1),
  6   new_marked   VARCHAR2(1),
  7   old_pcmac    VARCHAR2(1),
  8   new_pcmac    VARCHAR2(1),
  9   old_score    NUMBER,
 10   new_score    NUMBER,
 11   change_date  DATE,
 12   operation    VARCHAR2(6));

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER audit_game_players
  2  AFTER INSERT OR UPDATE OR DELETE ON game_player
  3  FOR EACH ROW
  4  BEGIN
  5    IF INSERTING THEN
  6      INSERT INTO game_player_audit(player_id,game_id,group_number,
  7                              new_marked,new_pcmac,new_score,
  8                              change_date,operation)
  9      VALUES(:new.player_id,:new.game_id,:new.group_number,
 10             :new.marked,:new.pcmac,:new.score,
 11             SYSDATE,'INSERT');
 12    ELSIF UPDATING THEN
 13      INSERT INTO game_player_audit(player_id,game_id,group_number,
 14                                old_marked,new_marked,
 15                                old_pcmac,new_pcmac,
 16                              old_score,new_score,
 17                              change_date,operation)
 18      VALUES(:new.player_id,:new.game_id,:new.group_number,
 19             :old.marked,:new.marked,
 20             :old.pcmac,:new.pcmac,
 21             :old.score,:new.score,
 22             SYSDATE,'UPDATE');
 23    ELSIF DELETING THEN
 24      INSERT INTO game_player_audit(player_id,game_id,group_number,
 25                              old_marked,old_pcmac,old_score,
 26                              change_date,operation)
 27      VALUES(:old.player_id,:old.game_id,:old.group_number,
 28             :old.marked,:old.pcmac,:old.score,
 29             SYSDATE,'DELETE');
 30    END IF;
 31  END;
 32  /

Trigger created.

SQL>
SQL>
SQL> drop table game_player;

Table dropped.

SQL>
SQL> drop table game_player_audit;

Table dropped.

SQL>