Implementar un disparador en Postgresql

[bettu]

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:

Código (sql) [Seleccionar] Expandir

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 (sql) [Seleccionar] Expandir

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,