How to Convert UPDATE Operation into INSERT Using Triggers in PostgreSQL

postgresqltrigger

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

Best Answer

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!

Related Question