From 764db09a6e620f25519cd05da63f7740c857f6f4 Mon Sep 17 00:00:00 2001
From: Ant Zucaro <azucaro@gmail.com>
Date: Wed, 9 Nov 2011 18:15:55 -0500
Subject: [PATCH] Fix the triggers for partitioning.

---
 triggers/games_ins_trg.sql               | 25 ++++++++++++++----------
 triggers/player_game_stats_ins_trg.sql   | 25 ++++++++++++++----------
 triggers/player_weapon_stats_ins_trg.sql | 25 ++++++++++++++----------
 3 files changed, 45 insertions(+), 30 deletions(-)

diff --git a/triggers/games_ins_trg.sql b/triggers/games_ins_trg.sql
index 17e7c08..e893c39 100755
--- a/triggers/games_ins_trg.sql
+++ b/triggers/games_ins_trg.sql
@@ -1,23 +1,23 @@
-CREATE OR REPLACE FUNCTION games_ins()
+CREATE OR REPLACE FUNCTION games_ins()
 RETURNS TRIGGER AS $$
 BEGIN
-    IF (create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01') THEN
+    IF (NEW.create_dt >= DATE '2011-04-01' AND NEW.create_dt < DATE '2011-07-01') THEN
         INSERT INTO games_2011Q2 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2011-07-01' AND NEW.create_dt < DATE '2011-10-01') THEN
         INSERT INTO games_2011Q3 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2011-10-01' AND NEW.create_dt < DATE '2012-01-01') THEN
         INSERT INTO games_2011Q4 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-01-01' AND NEW.create_dt < DATE '2012-04-01') THEN
         INSERT INTO games_2012Q1 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-04-01' AND NEW.create_dt < DATE '2012-07-01') THEN
         INSERT INTO games_2012Q2 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-07-01' AND NEW.create_dt < DATE '2012-10-01') THEN
         INSERT INTO games_2012Q3 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-10-01' AND NEW.create_dt < DATE '2013-01-01') THEN
         INSERT INTO games_2012Q4 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2013-01-01' AND NEW.create_dt < DATE '2013-04-01') THEN
         INSERT INTO games_2013Q1 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2013-04-01' AND NEW.create_dt < DATE '2013-07-01') THEN
         INSERT INTO games_2013Q2 VALUES (NEW.*);
     ELSE
         RAISE EXCEPTION 'Date out of range.  Fix the games_ins() trigger!';
@@ -26,3 +26,8 @@ BEGIN
 END;
 $$
 LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS games_ins_trg ON xonstat.games;
+CREATE TRIGGER games_ins_trg
+    BEFORE INSERT on xonstat.games
+    FOR EACH ROW EXECUTE PROCEDURE games_ins();
diff --git a/triggers/player_game_stats_ins_trg.sql b/triggers/player_game_stats_ins_trg.sql
index 8c818f9..e321a30 100755
--- a/triggers/player_game_stats_ins_trg.sql
+++ b/triggers/player_game_stats_ins_trg.sql
@@ -1,23 +1,23 @@
-CREATE OR REPLACE FUNCTION player_game_stats_ins()
+CREATE OR REPLACE FUNCTION player_game_stats_ins()
 RETURNS TRIGGER AS $$
 BEGIN
-    IF (create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01') THEN
+    IF (NEW.create_dt >= DATE '2011-04-01' AND NEW.create_dt < DATE '2011-07-01') THEN
         INSERT INTO player_game_stats_2011Q2 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2011-07-01' AND NEW.create_dt < DATE '2011-10-01') THEN
         INSERT INTO player_game_stats_2011Q3 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2011-10-01' AND NEW.create_dt < DATE '2012-01-01') THEN
         INSERT INTO player_game_stats_2011Q4 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-01-01' AND NEW.create_dt < DATE '2012-04-01') THEN
         INSERT INTO player_game_stats_2012Q1 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-04-01' AND NEW.create_dt < DATE '2012-07-01') THEN
         INSERT INTO player_game_stats_2012Q2 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-07-01' AND NEW.create_dt < DATE '2012-10-01') THEN
         INSERT INTO player_game_stats_2012Q3 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-10-01' AND NEW.create_dt < DATE '2013-01-01') THEN
         INSERT INTO player_game_stats_2012Q4 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2013-01-01' AND NEW.create_dt < DATE '2013-04-01') THEN
         INSERT INTO player_game_stats_2013Q1 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2013-04-01' AND NEW.create_dt < DATE '2013-07-01') THEN
         INSERT INTO player_game_stats_2013Q2 VALUES (NEW.*);
     ELSE
         RAISE EXCEPTION 'Date out of range.  Fix the player_game_stats_ins() trigger!';
@@ -26,3 +26,8 @@ BEGIN
 END;
 $$
 LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS player_game_stats_ins_trg ON xonstat.player_game_stats;
+CREATE TRIGGER player_game_stats_ins_trg
+    BEFORE INSERT on xonstat.player_game_stats
+    FOR EACH ROW EXECUTE PROCEDURE player_game_stats_ins();
diff --git a/triggers/player_weapon_stats_ins_trg.sql b/triggers/player_weapon_stats_ins_trg.sql
index f209b01..fbaf451 100755
--- a/triggers/player_weapon_stats_ins_trg.sql
+++ b/triggers/player_weapon_stats_ins_trg.sql
@@ -1,23 +1,23 @@
-CREATE OR REPLACE FUNCTION player_weapon_stats_ins()
+CREATE OR REPLACE FUNCTION player_weapon_stats_ins()
 RETURNS TRIGGER AS $$
 BEGIN
-    IF (create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01') THEN
+    IF (NEW.create_dt >= DATE '2011-04-01' AND NEW.create_dt < DATE '2011-07-01') THEN
         INSERT INTO player_weapon_stats_2011Q2 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2011-07-01' AND NEW.create_dt < DATE '2011-10-01') THEN
         INSERT INTO player_weapon_stats_2011Q3 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2011-10-01' AND NEW.create_dt < DATE '2012-01-01') THEN
         INSERT INTO player_weapon_stats_2011Q4 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-01-01' AND NEW.create_dt < DATE '2012-04-01') THEN
         INSERT INTO player_weapon_stats_2012Q1 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-04-01' AND NEW.create_dt < DATE '2012-07-01') THEN
         INSERT INTO player_weapon_stats_2012Q2 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-07-01' AND NEW.create_dt < DATE '2012-10-01') THEN
         INSERT INTO player_weapon_stats_2012Q3 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2012-10-01' AND NEW.create_dt < DATE '2013-01-01') THEN
         INSERT INTO player_weapon_stats_2012Q4 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2013-01-01' AND NEW.create_dt < DATE '2013-04-01') THEN
         INSERT INTO player_weapon_stats_2013Q1 VALUES (NEW.*);
-    ELSIF (create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01') THEN
+    ELSIF (NEW.create_dt >= DATE '2013-04-01' AND NEW.create_dt < DATE '2013-07-01') THEN
         INSERT INTO player_weapon_stats_2013Q2 VALUES (NEW.*);
     ELSE
         RAISE EXCEPTION 'Date out of range.  Fix the player_weapon_stats_ins() trigger!';
@@ -26,3 +26,8 @@ BEGIN
 END;
 $$
 LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS player_weapon_stats_ins_trg ON xonstat.player_weapon_stats;
+CREATE TRIGGER player_weapon_stats_ins_trg
+    BEFORE INSERT on xonstat.player_weapon_stats
+    FOR EACH ROW EXECUTE PROCEDURE player_weapon_stats_ins();
-- 
2.39.2