I am using Triggers, cases and Exists for the first time, and cannot figure out the problem. I have replaced the table names are attributes for the responder's convenience. Essentially, what I want is that: When I insert an entry to my temporal table, if the new entry's primary key already exists in the temporal table, I want this new entry's start date to be the previous record's end date. if the new entry is completely new to the table, nothing happens (the insert operates as usual).
CREATE OR REPLACE FUNCTION update_End_Date() RETURNS trigger AS $$ BEGIN
SELECT CASE
WHEN EXISTS ( SELECT TemporalTable.primaryKey FROM TemporalTable WHERE primaryKey = NEW.primaryKey )
THEN
UPDATE
TemporalTable
SET
TemporalTable.DtEnd = NEW.DtStart
WHERE
TemporalTable.PrimaryKey = NEW.PrimaryKey AND
TemporalTable.DtEnd IS NULL
;
END
RETURN NEW;
RETURN NEW; END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER update_End_Date
BEFORE INSERT
ON Table1
FOR EACH ROW
EXECUTE PROCEDURE update_End_Date();
