Update main and second table with a before update trigger for message logging

Question

I currently have the following testing schema:

CREATE TABLE userMessage (
    message_id BIGSERIAL PRIMARY KEY,
    creator_id BIGINT NOT NULL REFERENCES "User" (id),
    created_at BIGINT NOT NULL,
    modified_at BIGINT NOT NULL,
    content TEXT NOT NULL
);
CREATE TABLE userMessageLog (
    message_id BIGINT PRIMARY KEY,
    modified_by BIGINT NOT NULL REFERENCES "User"(id),
    modified_at BIGINT NOT NULL,
    content TEXT NOT NULL,
foreign key (message_id) references userMessage (message_id)

);

Where who created the message, when and it's actual content. Then another table where I keep track of who modified (can be another user) the message, when and the previous content.

I also created the following trigger for when the message is first created:

CREATE OR REPLACE FUNCTION new_message() RETURNS trigger AS $$
    BEGIN
        INSERT INTO userMessageLog(message_id, modified_by, modified_at, content) VALUES (NEW.message_id, NEW.creator_id, NEW.created_at, NEW.content);
        RETURN NEW;
    END
    $$
    LANGUAGE plpgsql;
CREATE TRIGGER new_message AFTER INSERT ON userMessage FOR EACH ROW EXECUTE PROCEDURE new_message();

Which works as expected. As soon as something is inserted on userMessage a new log entry will appear on userMessageLog.

My question is, when the user or another user edits that message, how can I create a trigger and modify the content, modified_at columns from userMessage table and add a new entry to the userMessageLog? Is there a more better approach to this schema/trigger design?

My only approach resulted in a failed attempt of creating a trigger on BEFORE UPDATE for userMessage which would make the entry on userMessageLogand then UPDATE the userMessage table, but that would result in recursive calls because of the trigger and I also have no way of letting it know who modified it for modified_by and when it was modified for modified_at column.

score 0 · Answer 1 · answered Jun 16 '20 at 07:51

First of all, if you want to add a new entry log for each insert/update operation, you should change userMessageLog's primary key. Otherwise you'll receive an error message due to duplicity.

Then, you can deal with trigger recursion by using: pg_trigger_depth(). You can get some examples in this questions:

Having said that, you can find another questions with some discussions about the use of pg_trigger_depth(), with points for or against of using it. Have a look at the answers of Evan Carrol and Erwin Brandstetter in this question:

Is pg_trigger_depth() bad to use for preventing trigger cascading (recursion)?

Personally, I'll use a function instead of a trigger.

Update main and second table with a before update trigger for message logging

1 Answers1