summaryrefslogtreecommitdiff
path: root/sql/fixes/transactions_foreign_key_fix.sql
blob: 58dae8e2f0183875c99e7a1fd29f77918d637e13 (plain)
  1. DELETE FROM transactions;
  2. CREATE OR REPLACE FUNCTION track_global_sequence() RETURNS TRIGGER AS
  3. $$
  4. BEGIN
  5. IF tg_op = 'INSERT' THEN
  6. INSERT INTO transactions (id, table_name)
  7. VALUES (new.id, TG_RELNAME);
  8. ELSEIF tg_op = 'UPDATE' THEN
  9. IF new.id = old.id THEN
  10. return new;
  11. ELSE
  12. UPDATE transactions SET id = new.id WHERE id = old.id;
  13. END IF;
  14. ELSE
  15. DELETE FROM transactions WHERE id = old.id;
  16. END IF;
  17. RETURN new;
  18. END;
  19. $$ LANGUAGE PLPGSQL;
  20. INSERT INTO transactions (id, table_name) SELECT id, 'ap' FROM ap;
  21. DROP RULE ap_id_track_i ON ap;
  22. DROP RULE ap_id_track_u ON ap;
  23. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON ap
  24. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  25. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON ap
  26. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  27. INSERT INTO transactions (id, table_name) SELECT id, 'ar' FROM ar;
  28. DROP RULE ar_id_track_i ON ar;
  29. DROP RULE ar_id_track_u ON ar;
  30. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON ar
  31. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  32. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON ar
  33. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  34. INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business;
  35. DROP RULE business_id_track_i ON business;
  36. DROP RULE business_id_track_u ON business;
  37. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON business
  38. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  39. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON business
  40. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  41. INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart;
  42. DROP RULE chart_id_track_i ON chart;
  43. DROP RULE chart_id_track_u ON chart;
  44. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON chart
  45. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  46. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON chart
  47. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  48. INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer;
  49. DROP RULE customer_id_track_i ON customer;
  50. DROP RULE customer_id_track_u ON customer;
  51. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON customer
  52. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  53. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON customer
  54. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  55. INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department;
  56. DROP RULE department_id_track_i ON department;
  57. DROP RULE department_id_track_u ON department;
  58. INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee;
  59. DROP RULE employee_id_track_i ON employee;
  60. DROP RULE employee_id_track_u ON employee;
  61. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON employee
  62. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  63. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON employee
  64. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  65. INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
  66. DROP RULE gl_id_track_i ON gl;
  67. DROP RULE gl_id_track_u ON gl;
  68. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON gl
  69. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  70. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON gl
  71. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  72. INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe;
  73. DROP RULE oe_id_track_i ON oe;
  74. DROP RULE oe_id_track_u ON oe;
  75. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON oe
  76. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  77. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON oe
  78. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  79. INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts;
  80. DROP RULE parts_id_track_i ON parts;
  81. DROP RULE parts_id_track_u ON parts;
  82. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON parts
  83. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  84. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON parts
  85. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  86. INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup;
  87. DROP RULE partsgroup_id_track_i ON partsgroup;
  88. DROP RULE partsgroup_id_track_u ON partsgroup;
  89. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON partsgroup
  90. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  91. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON partsgroup
  92. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  93. INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup;
  94. DROP RULE pricegroup_id_track_i ON pricegroup;
  95. DROP RULE pricegroup_id_track_u ON pricegroup;
  96. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON pricegroup
  97. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  98. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON pricegroup
  99. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  100. INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project;
  101. DROP RULE project_id_track_i ON project;
  102. DROP RULE project_id_track_u ON project;
  103. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON project
  104. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  105. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON project
  106. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  107. INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor;
  108. DROP RULE vendor_id_track_i ON vendor;
  109. DROP RULE employee_id_track_u ON vendor;
  110. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON vendor
  111. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  112. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON vendor
  113. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  114. INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
  115. DROP RULE warehouse_id_track_i ON warehouse;
  116. DROP RULE warehouse_id_track_u ON warehouse;
  117. CREATE TRIGGER track_global_inserts_iu BEFORE INSERT OR UPDATE ON warehouse
  118. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();
  119. CREATE TRIGGER track_global_inserts_d AFTER DELETE ON warehouse
  120. FOR EACH ROW EXECUTE PROCEDURE track_global_sequence();