1

SOLVED
Edit: Trigger function and trigger are working fine with no errors, problem was with my database itself, dropped the database, created new, it's working now.

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();
  • Could you show us the Trigger that you have which prevents UPDATEs but also prevents INSERTs? – Vérace Apr 17 at 7:07
  • updated my question with the code. – dsnewguy Apr 17 at 7:20
  • That trigger definition will not prevent inserts. Maybe you have another trigger definition or the INSERT fails for some other reason. What is the exact error message you get when you try to INSERT? – a_horse_with_no_name Apr 17 at 8:01
  • @a_horse_with_no_name my bad, sorry. I have updated the question. I am worried if this question valid for this form anymore or not. please have a look – dsnewguy Apr 17 at 8:42
  • Then your "form" (whatever that is) uses an UPDATE, not an INSERT – a_horse_with_no_name Apr 17 at 8:45
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.