Postgresql – how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger

postgresqltrigger

How do I prevent UPDATE on any record in a given table, but allow INSERT, I already have created a trigger which prevents update but I found that it also prevents INSERT.

EDIT
I can INSERT new row/record from the database itself but not from the form for a new record of the application that is connected to this database, after submitting the form, the trigger is being executed.

It's a web application built with Django.

TRIGGER FUNCTION

CREATE OR REPLACE FUNCTION prevent_updte()
RETURNS trigger AS
$BODY$
BEGIN
RAISE EXCEPTION 'Data modification not allowed';
END;
$BODY$
LANGUAGE plpgsql

TRIGGER

CREATE Trigger prevent_update
BEFORE UPDATE ON purchases_purchase
FOR EACH ROW
EXECUTE PROCEDURE prevent_update();

Best Answer

You can use GRANT & REVOKE for this purpose:

 CREATE TABLE TEST (ID int, NAME text);

 REVOKE ALL ON TABLE TEST FROM CURRENT_USER;

 GRANT INSERT ON TABLE TEST TO CURRENT_USER;
 GRANT SELECT ON TABLE TEST TO CURRENT_USER;

 INSERT INTO TEST VALUES (1, 'NAME 1'),(2, 'NAME 2');

UPDATE TEST SET NAME='NAME 3' WHERE ID = 1;
ERROR:  permission denied for table test

db<>fiddle here

Related Solutions

Postgresql: Insert trigger function fails on partial insert statement

Try this form:

 EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW;

It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays.

An even more modern and cleaner version (quote the table's name if necessary):

 EXECUTE format('insert into %I values ($1.*)', child) USING NEW;

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Postgresql: Insert trigger function fails on partial insert statement

How to commit transaction on an after update event trigger

Related Question