diff options
Diffstat (limited to 'labs/2')
| -rw-r--r-- | labs/2/run.sql | 22 |
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(); |