Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

create table cart (
  cart_id int generated by default as identity (start with 100) primary key,
  at timestamp with time zone default now()
);

insert into cart (cart_id) 
     values (0);

select * 
  from cart; 

CREATE TABLE

INSERT 0 1

cart_id
at

0
2023-07-05 16:58:11.627617+01

SELECT 1

create table customer (
  customer_id int generated by default as identity (start with 100) primary key,
  at timestamp with time zone default now() 
);
insert into customer(customer_id) 
    values (default); 

select * 
  from customer; 

CREATE TABLE

INSERT 0 1

customer_id
at

100
2023-07-05 16:58:11.644631+01

SELECT 1

-- Create generic trigger function
create or replace function surrogate_id_guard() 
  returns trigger
  language plpgsql 
as $$
begin
  raise exception
    'trigger %: updating is prohibited for %',
    tg_name, tg_argv[0] using
    errcode = 'restrict_violation';
  return null;
end;
$$;

CREATE FUNCTION

-- create trigger for cart
create or replace trigger cart_id_guard
   before update of cart_id 
       on cart for each row
     when (old.cart_id is distinct from new.cart_id)
  execute function surrogate_id_guard('cart_id');

CREATE TRIGGER

-- update cart_id
update cart 
   set cart_id = 50
 where cart_id = 0;

ERROR:  trigger cart_id_guard: updating is prohibited for cart_id
CONTEXT:  PL/pgSQL function surrogate_id_guard() line 3 at RAISE

-- create trigger for customer
create or replace trigger customer_id_guard
   before update of customer_id 
       on customer for each row
     when (old.customer_id is distinct from new.customer_id)
  execute function surrogate_id_guard('customer_id');

CREATE TRIGGER

-- update customer_id
update customer 
  set customer_id = 50 
where customer_id = 100; 

ERROR:  trigger customer_id_guard: updating is prohibited for customer_id
CONTEXT:  PL/pgSQL function surrogate_id_guard() line 3 at RAISE

-- allow update of other columns
select * 
  from cart; 

update cart 
   set at = at - interval '1 day' ; 

select * 
  from cart; 

cart_id
at

0
2023-07-05 16:58:11.627617+01

SELECT 1

UPDATE 1

cart_id
at

0
2023-07-04 16:58:11.627617+01

SELECT 1

select * 
  from customer;

update customer
   set at = at - interval '1 day' ; 

select * 
  from customer; 

customer_id
at

100
2023-07-05 16:58:11.644631+01

SELECT 1

UPDATE 1

customer_id
at

100
2023-07-04 16:58:11.644631+01

SELECT 1