Mega Code Archive
Audit trigger
<![CDATA[
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> drop table game_player;
Table dropped.
SQL>
SQL> drop table game_player_audit;
Table dropped.
SQL>
]]>