4 ms·
Here's an implementation with lines of sql and pl/pgsql: Given a table mytable with an pk id and say the fields 'block' and 'hash', replace it with mytable_cha
by sfkeller 4y ago
Here's an implementation with lines of sql and pl/pgsql:
Given a table mytable with an pk id and say the fields 'block' and 'hash', replace it with mytable_changes and add an "internal" field called created_at of type "timestamp not null default now()".
Then create this trigger function (e.g. PostgreSQL):
create function mytable_changes_trigger() returns trigger as $$
begin
if (tg_op = 'insert') then
insert into mytable_changes (block, hash)
values (new.block, hash);
return new;
if (tg_op = 'delete') then
insert into mytable_changes (block, hash)
values (old.block, old.hash);
return old;
elsif (tg_op = 'update') then
insert into mytable_changes (block, hash)
values (old.block, old.hash);
return new;
end if;
end;
$$ language plpgsql;
And create this trigger:
create trigger mytable_changes
after insert, update or delete on mytable
for each row
execute function mytable_changes_trigger();
What's left as an exercise is that the hash of the prev. block is being preprended.