|
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>
|