summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--sql/fixes/transactions_foreign_key_fix.sql178
1 files changed, 178 insertions, 0 deletions
diff --git a/sql/fixes/transactions_foreign_key_fix.sql b/sql/fixes/transactions_foreign_key_fix.sql
new file mode 100644
index 00000000..5b0ed2dd
--- /dev/null
+++ b/sql/fixes/transactions_foreign_key_fix.sql
@@ -0,0 +1,178 @@
+DELETE FROM transactions;
+
+CREATE OR REPLACE FUNCTION track_global_sequence() RETURNS TRIGGER AS
+$$
+BEGIN
+ IF tg_op = 'INSERT' THEN
+ INSERT INTO transactions (id, table_name)
+ VALUES (new.id, TG_RELNAME);
+ ELSEIF tg_op = 'UPDATE' THEN
+ IF new.id = old.id THEN
+ return new;
+ ELSE
+ UPDATE transactions SET id = new.id WHERE id = old.id;
+ END IF;
+ ELSE
+ DELETE FROM transactions WHERE id = old.id;
+ END IF;
+ RETURN new;
+END;
+$$ LANGUAGE PLPGSQL;
+
+insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
+DROP RULE ap_id_track_i ON ap;
+DROP RULE ap_id_track_u ON update TO ap;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON ap
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON ap
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+insert into transactions (id, table_name) SELECT id, 'ar' FROM ar;
+
+DROP RULE ar_id_track_i ON insert TO ar;
+DROP RULE ar_id_track_u ON update TO ar;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON ar
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON ar
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business;
+
+DROP RULE business_id_track_i ON insert TO business;
+DROP RULE business_id_track_u ON update TO business
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON business
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON business
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart;
+
+DROP RULE chart_id_track_i ON insert TO chart;
+DROP RULE chart_id_track_u ON update TO chart;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON chart
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON chart
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer;
+
+DROP RULE customer_id_track_i ON insert TO customer;
+DROP RULE customer_id_track_u ON update TO customer;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON customer
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON customer
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department;
+
+DROP RULE department_id_track_i ON insert TO department;
+DROP RULE department_id_track_u ON update TO department;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee;
+
+DROP RULE employee_id_track_i ON insert TO employee;
+DROP RULE employee_id_track_u ON update TO employee;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON employee
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON employee
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
+
+DROP RULE gl_id_track_i ON insert TO gl;
+DROP RULE gl_id_track_u ON update TO gl;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON gl
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON gl
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe;
+
+DROP RULE oe_id_track_i ON insert TO oe;
+DROP RULE oe_id_track_u ON update TO oe;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON oe
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON oe
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts;
+
+DROP RULE parts_id_track_i ON insert TO parts;
+DROP RULE parts_id_track_u ON update TO parts;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON parts
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON parts
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup;
+
+DROP RULE partsgroup_id_track_i ON insert TO partsgroup;
+DROP RULE partsgroup_id_track_u ON update TO partsgroup;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON partsgroup
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON partsgroup
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup;
+
+DROP RULE pricegroup_id_track_i ON insert TO pricegroup;
+DROP RULE pricegroup_id_track_u ON update TO pricegroup;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON pricegroup
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON pricegroup
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project;
+
+DROP RULE project_id_track_i ON insert TO project;
+DROP RULE project_id_track_u ON update TO project;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON project
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON project
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor;
+
+DROP RULE vendor_id_track_i ON insert TO vendor;
+DROP RULE employee_id_track_u ON update TO vendor;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON vendor
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON vendor
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
+
+DROP RULE warehouse_id_track_i ON insert TO warehouse;
+DROP RULE warehouse_id_track_u ON update TO warehouse;
+
+CREATE TRIGGER track_global_inserts BEFORE INSERT OR UPDATE ON warehouse
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
+
+CREATE TRIGGER track_global_inserts AFTER DELETE ON warehouse
+FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();