Use a trigger to cascase foreign key updates instead of a foreign key constraint

1 files changed, 22 insertions, 3 deletions

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();