How to convert UPDATE operation into INSERT using triggers

Question

Given I have a table User with column status (active/inactive)

these users may be attached with some other entities, so I do not want them to get modified unless explicitly specified. and for attaching to newer entities I only need the latest updated ones.

So when an UPDATE is performed, I want to make the existing row inactive, and INSERT the updated values as a new row.

I am using PostgreSQL

• Akina: CREATE TRIGGER .. INSTEAD OF UPDATE. Check if any field except status is modified.
• Laurenz Albe: @Akina Good idea; what about recursion?
• Lennart - Slava Ukraini: Check out temporal tables
• Akina: These triggers allows to specify the list of updated fields it is affected on. INSTEAD OF UPDATE OF column_name1 [, column_name2 ... ]
• user1822: @Akina: instead of triggers can only be defined on VIEWs, not regular tables

Answer 1

This is not very complicated to do. If a BEFORE trigger returns a NULL value, this prevents the UPDATE from taking place.

So in the trigger function you can check the conditions if an UPDATE is allowed. If not, Run an UPDATE statement that marks the row as inactive and do an INSERT.

Something along the lines (not tested!):

create or replace function prevent_update() 
 returns trigger
as
$$
begin
 -- adjust this check to whatever you want to prevent
 -- but make sure to only enter this part if the status is not inactive.
 -- you might want to check pg_trigger_depth() as well to make sure you never create an endless loop
 if old is distinct from new and new.status <> 'inactive' then 
 -- The row was changed, but status column is the same, so we want to mark 
 -- the row as inactive 
 -- Because the new status is 'inactive' the trigger that is now fired for this UPDATE statement
 -- will not do anything because we only do this for status other than 'inactive'
 UPDATE the_table
 SET status = 'inactive'
 WHERE id = new.id; --<< the primary key column
 INSERT INTO the_table (id, some_column, status)
 VALUES (default, new.some_column, 'active');
 -- do NOT proceed with original UPDATE
 return null; 
 end if;
 -- all OK, proceed with the update
 return new;
end;
$$
language plpgsql;

And the trigger definition:

create trigger prevent_update_trigger
 before update on the_table
 for each row execute procedure prevent_update();

---

I would be very careful to deploy something like this. This kind of "magic" in the background might be the cause of a lot of confusion and trouble, so use with care!

• Jebil: instead INSERT INTO the_table (id, some_column, status) VALUES (default, new.some_column, 'active'); how can I just insert whatever is there, basically without having to specify column names

Answer 2

Pasting here the code i developed using a_horse_with_no_name's answer , if that helps someone, basically adding more dynamic queries, and table level lock to prevent race conditions. Any suggestions on improvement are welcome.

create or replace function prevent_update_do_insert() 
returns trigger as 
$$ 
declare 
 pk_column text;
 other_columns text[];
begin 
 -- the purpose of this function is to prevent updates coming into tables which has a status column.
 -- treat any incoming update requests as, deactivate the existing then insert updated values as new row.
PERFORM pg_advisory_lock(quote_ident(TG_TABLE_NAME)); -- add lock prevent race conditions, any other concurrent invokations should wait. 
 -- make sure to only enter if existing status is active.
 -- we do not want to disturb inactive entries.
if old.status <> 'active' then 
 return null;
end if;
-- make sure to only enter this part if the status is not inactive.
if old is distinct from new 
 and new.status <> 'inactive' then 
 -- The row was changed, but status column is the same, so we want to mark the row as inactive 
 SELECT 
 a.attname into pk_column -- The primary key column
 FROM 
 pg_index i 
 JOIN pg_attribute a ON a.attrelid = i.indrelid 
 AND a.attnum = ANY(i.indkey) 
 WHERE 
 i.indrelid = TG_RELID 
 AND i.indisprimary;
 -- Because the new status is 'inactive' the trigger that is now fired for this UPDATE statement
 -- will not do anything because we only do this for status other than 'inactive'
 execute (
 'UPDATE ' || quote_ident(TG_TABLE_NAME)|| ' SET status = ''INACTIVE'' WHERE ' || quote_ident(pk_column)|| ' = ' || new.id
 );
 SELECT 
 array_agg(attname) INTO other_columns -- All other columns except primary key.
 FROM 
 pg_attribute 
 WHERE 
 attrelid = TG_RELID 
 AND attnum > 0 
 AND NOT attisdropped 
 AND attname not in (pk_column);
 -- Insert the UPDATE row as new row
 EXECUTE (
 ' INSERT INTO ' || quote_ident(TG_TABLE_NAME)|| '(' || array_to_string(other_columns, ',')|| ' ) VALUES (1ドル.' || array_to_string(other_columns, ',1ドル.') || ')'
 ) using new;
 -- do NOT proceed with original UPDATE
 return null;
end if;
-- all OK, proceed with the update
return new;
PERFORM pg_advisory_unlock(quote_ident(TG_TABLE_NAME)); -- unlock table level lock
end;
$$ 
language plpgsql;

And then add trigger

create trigger prevent_update_trigger
 before update on the_table
 for each row execute procedure prevent_update_do_insert();