Tomislav Pertinac Tomislav Pertinac - 1 month ago 11
SQL Question

Trigger check if some values are equal in some row in table then update if not then insert

I'm having problem with trigger in psql which needs to check if some values already exist(sender, reciever, subject) in table and if they exist i only need to update one value(subject = RE: +subject) in that row, if not then I need to insert new row.

CREATE OR REPLACE FUNCTION check_theme()
RETURNS TRIGGER
AS $$
DECLARE e BOOLEAN;
BEGIN
e := EXISTS(
SELECT *
FROM message
WHERE sender = NEW.sender
AND reciever = NEW.reciever
AND subject = NEW.subject
);
IF NOT e THEN
INSERT INTO message(sender, reciever, subject, text)
VALUES(NEW.sender, NEW.reciever, NEW.subject, NEW.text);
ELSE
UPDATE message
SET subject = 'Re: ' || NEW.subject
WHERE sender = NEW.sender
AND reciever = NEW.reciever
AND subject = NEW.subject;
END IF;
RETURN NEW;

END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER theme_check
BEFORE INSERT OR UPDATE
ON message
FOR EACH ROW EXECUTE PROCEDURE check_theme();


So after I want to insert new row which already have same values for sender, reciever and subject trigger gets in infinitive loop.

What am I doing wrong?

Answer

After few days I have figured out that my knowledge of how triggers work was wrong.. so my mistake was that I was adding INSERT function but INSERT will be done anyway if there wasn't message with same sender, recipient and title.That's why I was always getting infinitive loop. So the code that will work is belove

CREATE OR REPLACE FUNCTION provjera_teme()
RETURNS TRIGGER
AS $$
    DECLARE postoji BOOLEAN;
    BEGIN
        postoji := EXISTS(
            SELECT poruka
            FROM poruka
            WHERE posiljatelj = NEW.posiljatelj
              AND primatelj = NEW.primatelj
              AND (naslov = NEW.naslov OR naslov LIKE 'Re: ' || NEW.naslov)
            );    
        IF postoji THEN
            UPDATE poruka
            SET naslov = 'Re: ' || naslov
            WHERE posiljatelj = NEW.posiljatelj
                  AND primatelj = NEW.primatelj
                  AND naslov = NEW.naslov AND naslov NOT LIKE 'Re%';
                RETURN NULL;
        END IF;
        RETURN NEW;
    END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER tema_provjera
BEFORE INSERT
ON poruka
FOR EACH ROW EXECUTE PROCEDURE provjera_teme();