From b75e40f82425105f9ce5658dddf623cd2ae3fb60 Mon Sep 17 00:00:00 2001
From: mo khan <mo.khan@gmail.com>
Date: Wed, 1 Jan 2020 12:30:13 -0700
Subject: Use a trigger to cascase foreign key updates instead of a foreign key
 constraint

---
 labs/2/run.sql | 25 ++++++++++++++++++++++---
 1 file changed, 22 insertions(+), 3 deletions(-)

(limited to 'labs/2/run.sql')

diff --git a/labs/2/run.sql b/labs/2/run.sql
index 0b3f09c..9e9bacc 100644
--- a/labs/2/run.sql
+++ b/labs/2/run.sql
@@ -57,6 +57,25 @@ INSERT INTO locations VALUES (12, 'Ottawa');
 INSERT INTO locations VALUES (12, 'Montreal');
 
 ALTER TABLE departments ADD CONSTRAINT employees_fk FOREIGN KEY (mgrsin) REFERENCES employees (sin);
-ALTER TABLE employees ADD CONSTRAINT departments_fk FOREIGN KEY (dno) REFERENCES departments (dnumber);
-ALTER TABLE locations ADD CONSTRAINT departments_fk FOREIGN KEY (dnbr) REFERENCES departments(dnumber);
-ALTER TABLE projects ADD CONSTRAINT departments_fk FOREIGN KEY (dnum) REFERENCES departments (dnumber);
+-- ALTER TABLE employees ADD CONSTRAINT departments_fk FOREIGN KEY (dno) REFERENCES departments (dnumber);
+-- ALTER TABLE locations ADD CONSTRAINT departments_fk FOREIGN KEY (dnbr) REFERENCES departments(dnumber);
+-- ALTER TABLE projects ADD CONSTRAINT departments_fk FOREIGN KEY (dnum) REFERENCES departments (dnumber);
+
+CREATE OR REPLACE FUNCTION update_referenced_department_numbers() RETURNS trigger AS $$
+BEGIN
+  IF NEW.dnumber <> OLD.dnumber THEN
+    UPDATE employees SET dno = NEW.dnumber WHERE dno = OLD.dnumber;
+    UPDATE locations SET dnbr = NEW.dnumber WHERE dnbr = OLD.dnumber;
+    UPDATE projects SET dnum = NEW.dnumber WHERE dnum = OLD.dnumber;
+  END IF;
+
+  RETURN NEW;
+END
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS update_department_number on departments CASCADE;
+CREATE TRIGGER update_department_number
+  AFTER UPDATE
+  ON departments
+  FOR EACH ROW
+  EXECUTE PROCEDURE update_referenced_department_numbers();
-- 
cgit v1.2.3

