I'm trying to implement properly a trigger function in Postgresql. The database consists of three tables, two of which are connected by a third one which is an intermediary:

Table MUSICIAN (PK = id_musician)

TABLE COMPOSER (PK = FK = id_musician, id_song)

TABLE SONG (PK = id_song; FK = id_album)

The trigger function is implemented on the table SONG for calculating the column num_awards in the table MUSICIAN and the results expected are as follows:

As you can see, the column num_awards sums the awards from all the songs present in the table SONG for each composer. For that purpose I've coded the following trigger function:

CREATE OR REPLACE FUNCTION update_num_awards()
RETURNS trigger AS $$ 
BEGIN
    CREATE TABLE IF NOT EXISTS bakcomp AS TABLE COMPOSER;
    CREATE TABLE IF NOT EXISTS baksong AS TABLE SONG;
    IF (TG_OP = 'INSERT') THEN 
        UPDATE MUSICIAN
        SET num_awards = num_awards   NEW.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM COMPOSER AS c JOIN SONG
                            ON(c.id_song = NEW.id_song));
    ELSIF (TG_OP = 'UPDATE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards   NEW.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM COMPOSER AS c JOIN SONG
                            ON(c.id_song = NEW.id_song));
        UPDATE MUSICIAN
        SET num_awards = num_awards - OLD.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM bakcomp AS c JOIN baksong
                            ON(c.id_song = OLD.id_song));   
    ELSIF (TG_OP = 'DELETE') THEN
        UPDATE MUSICIAN
        SET num_awards = num_awards - OLD.awards
        WHERE id_musician IN (SELECT c.id_musician
                            FROM bakcomp AS c JOIN baksong
                            ON(c.id_song = OLD.id_song));
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_update_num_awards AFTER INSERT OR DELETE OR UPDATE ON SONG 
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE update_num_awards();

The function is triggered AFTER insert, delete or update in the table SONG. When inserting, coding seems pretty straightforward but when it comes to deleting things start to get difficult - the subquery I've coded doesn't work because rows may have already disappeared from the COMPOSER and SONG tables. I've tried to use temporary tables but they don't seem to work - somehow they vanish before the delete operation begins. So the only solution I've got through with is creating two permanent auxiliary tables, bakcomp and baksong, to make a copy of COMPOSER and SONG with before each delete operation.

And my question is, how could temporary tables be performed in this case? Personally, despite my piece of code working correctly for what I want, I would like to improve it or to make it more simple and elegant.

Any help would be greatly appreciated.

Regards

CodePudding user response：

It seems like doing this on song is doing it the hard way. It's composer that has most of the effects. Delete and insert are easy. Update can be treated as a delete insert.

• Composer
  • on delete: musician.num_awards = musician.num_awards - song.awards
  • on insert: musician.num_awards = musician.num_awards song.awards
  • on update
    • old.musician.num_awards = old.musician.num_awards - old.song.awards
    • new.musician.num_awards = new.musician.num_awards new.song.awards

When a song is inserted, it doesn't matter because it is not associated with a musician. When a song is deleted, it will cascade to delete the associated composers (assuming you've set up cascade deletes). That leaves one trigger, when a song is updated.

• Song
  • on update: for all musicians who composed the song, musician.num_awards = musician.num_awards - old.awards new.awards

No temp tables should be necessary.