CODEWITHSUNDEEP
sqlpostgresqltriggerspostgresql-10postgresql-triggers
Niv
Niv

Reputation: 291

insert trigger to manipulate timestamps of existing rows

I have a table with 2 date columns

CREATE TABLE test
(
  id serial PRIMARY KEY,
  code integer NOT NULL,
  eff_date timestamp default now(),
  exp_date timestamp default '2025-12-31'
);

I want to update the exp_date of an existing row when a new row with the same code is being inserted, the exp_date of the old row would be a day before the eff_date of the new row. Neither the eff_date or exp_date values would be in the insert query

For example:

id code eff_date exp_date
1 12345 2021-01-31 2021-02-27
2 12345 2021-02-28 2021-03-30
3 12345 2021-03-31 2021-04-29
4 12345 2021-04-30 2021-05-30
5 12345 2021-05-31 2025-12-31

In this table, we want to update row with id=1 when row id=2 is being inserted by checking the latest existing row (with most recent eff_date) and updating it's exp_date to one day prior to eff_date of new row.

exp_date for id=1 would become 2021-02-27 because eff_date of new row is 2021-02-28.

Can this be done through an insert trigger?

Upvotes: 0

Views: 175

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13069

Yes, you can do this with a trigger. eff_date and exp_date - although missing in the insert statement - will still be there with default values in the new record.

create or replace function test_insert_tf() returns trigger language plpgsql as 
$$
begin 
    update test 
    set exp_date = new.eff_date::date - 1 
    where code = new.code 
    and eff_date = (select max(eff_date) from test where code = new.code);
    return new;
end;
$$;

CREATE TRIGGER test_insert_t
    before INSERT
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE test_insert_tf();

Not very performant though. Btw is there a specific reason for eff_date and exp_date to be of type timestamp? Maybe type date would be more relevant.

Upvotes: 1

Related Questions