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
My question is, when the user or another user edits that message, how can I create a trigger and modify the
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
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