clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130830 fiddles created (16564 in the last week).

CREATE TABLE userMessage ( message_id BIGINT PRIMARY KEY, creator_id BIGINT NOT NULL, created_at timestamp NULL, modified_at timestamp NULL, content TEXT NOT NULL ); CREATE TABLE userMessageLog ( entry_id BIGSERIAL, message_id BIGINT, modified_by BIGINT NOT NULL, modified_at timestamp NOT NULL, content TEXT NOT NULL, PRIMARY KEY (message_id, entry_id), foreign key (message_id) references userMessage (message_id) );
 hidden batch(es)


CREATE OR REPLACE FUNCTION new_message() RETURNS trigger AS $$ BEGIN /* Always add a new entry to logs table */ INSERT INTO userMessageLog(message_id, modified_by, modified_at, content) VALUES (NEW.message_id, NEW.creator_id, current_timestamp, NEW.content); IF TG_OP = 'UPDATE' THEN UPDATE userMessage SET modified_at = current_timestamp WHERE message_id = NEW.message_id; END IF; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER new_message AFTER INSERT OR UPDATE ON userMessage FOR EACH ROW WHEN (pg_trigger_depth() = 0) EXECUTE PROCEDURE new_message();
 hidden batch(es)


INSERT INTO userMessage VALUES (1, 100, current_timestamp, null, 'some text'); INSERT INTO userMessage VALUES (2, 200, current_timestamp, null, 'some text');
1 rows affected
1 rows affected
 hidden batch(es)


UPDATE userMessage SET content = 'another text' WHERE message_id = 1;
1 rows affected
 hidden batch(es)


SELECT * FROM userMessage; SELECT * FROM userMessageLog;
message_id creator_id created_at modified_at content
2 200 2020-06-16 08:27:24.378863 some text
1 100 2020-06-16 08:27:24.378863 2020-06-16 08:27:24.381258 another text
entry_id message_id modified_by modified_at content
1 1 100 2020-06-16 08:27:24.378863 some text
2 2 200 2020-06-16 08:27:24.378863 some text
3 1 100 2020-06-16 08:27:24.381258 another text
 hidden batch(es)