From b75e40f82425105f9ce5658dddf623cd2ae3fb60 Mon Sep 17 00:00:00 2001 From: mo khan 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') 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