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 userMessageLog
and 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.
Best Answer
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:
Personally, I'll use a function instead of a trigger.