Buenas!
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:
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?
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,
Mostrar Mensajes
