CREATE TABLE Critical_Spares ( part_id SERIAL PRIMARY KEY, part_name VARCHAR(100), part_code VARCHAR(50) UNIQUE, criticality INT CHECK (criticality BETWEEN 1 AND 5), current_stock INT, min_threshold INT, max_threshold INT, primary_sup INT REFERENCES Suppliers, secondary_sup INT REFERENCES Suppliers, tertiary_sup INT REFERENCES Suppliers, lead_time_days INT, unit_cost DECIMAL(10,2), last_pr_date DATE ); -- Auto-PR trigger CREATE OR REPLACE FUNCTION check_min_stock() RETURNS TRIGGER AS $$ BEGIN IF NEW.current_stock <= NEW.min_threshold THEN INSERT INTO Purchase_Requests (part_id, qty_requested, auto_raised) VALUES (NEW.part_id, NEW.max_threshold - NEW.current_stock, TRUE); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_auto_pr AFTER UPDATE OF current_stock ON Critical_Spares FOR EACH ROW EXECUTE FUNCTION check_min_stock();