Trigger for certain columns : After Event Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. After Event Trigger

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>
SQL> CREATE OR REPLACE TRIGGER audit_update
  2  AFTER UPDATE OF marked, pcmac, SCORE ON game_player
  3  REFERENCING OLD AS prior_to_cheat NEW AS after_cheat
  4  FOR EACH ROW
  5  WHEN ( prior_to_cheat.marked != after_cheat.marked OR
  6         prior_to_cheat.pcmac != after_cheat.pcmac OR
  7         prior_to_cheat.score != after_cheat.score )
  8  BEGIN
  9    INSERT INTO game_player_AUDIT(player_id,game_id,group_number,
 10                            old_marked,new_marked,
 11                            old_pcmac,new_pcmac,
 12                            old_score,new_score,
 13                            change_date,operation)
 14    VALUES(:after_cheat.player_id,:after_cheat.game_id,:after_cheat.group_number,
 15           :prior_to_cheat.marked,:after_cheat.marked,
 16           :prior_to_cheat.pcmac,:after_cheat.pcmac,
 17           :prior_to_cheat.score,:after_cheat.score,
 18           SYSDATE,'UPDATE');
 19  END;
 20  /

Trigger created.

SQL>
SQL>
SQL>
SQL>
SQL> drop table game_player;

Table dropped.

SQL>
SQL> drop table game_player_audit;

Table dropped.

SQL>