Estoy intentando implementar un disparador (o disparadores) en Postgresql. Me piden que se mantenga correctamente actualizada la columna number_toys de la tabla LETTER. Se quiere mantener el cálculo de nombre de juguetes frente a posibles cambios que se puedan producir a los juguetes pedidos en las cartas. Se puede suponer que los usuarios o programas no actualizarán nunca directamente la columna number_toys de la tabla LETTER, y que en el momento de insertar una nueva carta, el valor de la columna number_toy serà 0.
El schema que tengo es el siguiente:
Código
BEGIN WORK; SET TRANSACTION READ WRITE; SET datestyle = DMY; CREATE TABLE CHILD( child_id SMALLINT, child_name VARCHAR(255) NOT NULL, birth_date DATE NOT NULL, gender VARCHAR(255) NOT NULL, address VARCHAR(255), city VARCHAR(255), CONSTRAINT PK_CHILD PRIMARY KEY(child_id), CONSTRAINT VALID_GENDER CHECK (gender IN ('m', 'f')), CONSTRAINT VALID_DATE CHECK (birth_date <= now()) ); CREATE TABLE SIBLING( child_id1 SMALLINT, child_id2 SMALLINT, CONSTRAINT PK_SIBLING PRIMARY KEY(child_id1, child_id2), CONSTRAINT CHILD1_FK FOREIGN KEY (child_id1) REFERENCES CHILD(child_id), CONSTRAINT CHILD2_FK FOREIGN KEY (child_id2) REFERENCES CHILD(child_id) ); CREATE TABLE LETTER( letter_id SMALLINT, arrival_date DATE NOT NULL DEFAULT NOW(), number_toys INTEGER NOT NULL DEFAULT 0, child_id SMALLINT, CONSTRAINT PK_LETTER PRIMARY KEY(letter_id), CONSTRAINT CHILD_FK FOREIGN KEY (child_id) REFERENCES CHILD(child_id), CONSTRAINT VALID_CHILD_ID CHECK (child_id IS NOT NULL) ); CREATE TABLE TOY( toy_id SMALLINT, toy_name VARCHAR(255) NOT NULL, price REAL NOT NULL, toy_type VARCHAR(255) NOT NULL, manufacturer VARCHAR(255), CONSTRAINT PK_TOY PRIMARY KEY(toy_id), CONSTRAINT POSITIVE_PRICE CHECK (price > 0), CONSTRAINT VALID_TYPE CHECK(toy_type IN ('symbolic', 'rule', 'educational', 'cooperative', 'other')) ); CREATE TABLE WISHED_TOY( letter_id SMALLINT, toy_id SMALLINT, CONSTRAINT PK_WISHED_TOY PRIMARY KEY(letter_id, toy_id), CONSTRAINT LETTER_FK FOREIGN KEY (letter_id) REFERENCES LETTER(letter_id), CONSTRAINT TOY_FK FOREIGN KEY (toy_id) REFERENCES TOY(toy_id) ); CREATE TABLE FAMILY_TOYS( child_id SMALLINT, sibling_id SMALLINT, letter_id SMALLINT, toy_id SMALLINT, CONSTRAINT PK_FAMILY_TOYS PRIMARY KEY(child_id, sibling_id, letter_id, toy_id), CONSTRAINT FAMILY_FK_SIBLING FOREIGN KEY (child_id,sibling_id) REFERENCES SIBLING(child_id1, child_id2), CONSTRAINT FAMILY_FK_LETTER FOREIGN KEY (letter_id) REFERENCES LETTER(letter_id), CONSTRAINT FAMILY_FK_TOY FOREIGN KEY (toy_id) REFERENCES TOY(toy_id) ); COMMIT;
Por ahora lo que tengo es esto, pero no es correcto y no sé que es lo que me falta/falla. ¿Alguna alma caritativa por favor?
Código
BEGIN RAISE INFO ''update_numbertoys_letter execution''; IF (TG_OP = ''UPDATE'') THEN PERFORM update_numbertoys_by_letter(NEW.child_id); IF(NEW.child_id <> OLD.child_id) THEN PERFORM update_numbertoys_by_letter(OLD.child_id); END IF; RETURN NULL; ELSIF (TG_OP = ''INSERT'') THEN PERFORM update_numbertoys_by_letter(NEW.child_id); ELSIF (TG_OP = ''DELETE'') THEN PERFORM update_numbertoys_by_letter(OLD.child_id); END IF; RETURN NULL; END; ' LANGUAGE plpgsql; CREATE TRIGGER trigger_update_numbertoys_letter AFTER INSERT OR DELETE OR UPDATE OF child_id ON child FOR EACH ROW EXECUTE PROCEDURE update_numbertoys_letter(); COMMIT;
Gracias,