diff options
| -rw-r--r-- | labs/2/run.sql | 25 |
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(); |