use a trigger to update foreign key references instead of a foreign key constraint

1 files changed, 19 insertions, 3 deletions

diff --git a/labs/2/run.sql b/labs/2/run.sql
index 9e9bacc..3f32451 100644
--- a/labs/2/run.sql
+++ b/labs/2/run.sql
@@ -56,7 +56,7 @@ INSERT INTO locations VALUES (11, 'Athabasca');
 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 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);
@@ -74,8 +74,24 @@ END
 $$ LANGUAGE plpgsql;
 
 DROP TRIGGER IF EXISTS update_department_number on departments CASCADE;
-CREATE TRIGGER update_department_number
-  AFTER UPDATE
+CREATE TRIGGER update_department_number AFTER UPDATE
   ON departments
   FOR EACH ROW
+  WHEN (OLD.dnumber <> NEW.dnumber)
   EXECUTE PROCEDURE update_referenced_department_numbers();
+
+CREATE OR REPLACE FUNCTION update_referenced_sin_numbers() RETURNS trigger AS $$
+BEGIN
+  IF NEW.sin <> OLD.sin THEN
+    UPDATE departments set mgrsin = NEW.sin WHERE mgrsin = OLD.sin;
+  END IF;
+  RETURN NEW;
+END
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS update_sin ON employees CASCADE;
+CREATE TRIGGER update_sin AFTER UPDATE
+  ON employees
+  FOR EACH ROW
+  WHEN (OLD.sin <> NEW.sin)
+  EXECUTE PROCEDURE update_referenced_sin_numbers();