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.

Answer 1

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:

• Prevent recursive trigger in PostgreSQL.
• Or at this fiddle I've set up using your table definitions.

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.