CODEWITHSUNDEEP
postgresqlplpgsqldatabase-trigger
Suniel
Suniel

Reputation: 1499

Prevent deletion of table records for one user and allow deletion for another in postgresql

I have a requirement that i have to disable deletion of table records for all users except one user i.e user1. So for that i have used triggers which is as follows.

CREATE OR REPLACE FUNCTION prevent_deletion() RETURNS trigger AS $$
declare
cur_user varchar(30);
BEGIN
Select current_user into cur_user;
IF cur_user != 'user1' THEN
RAISE EXCEPTION 'You cannot delete records from this table!';
END IF;  
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The trigger is preventing from deleting table records for users other than user1 which is fine but its not working for user user1. I mean its not deleting the table records for user1.

Where am i wrong in my code?
Thanks in advance.

Upvotes: 2

Views: 800

Answers (1)

klin
klin

Reputation: 121754

A trigger executed before delete should return old.

CREATE OR REPLACE FUNCTION prevent_deletion() 
RETURNS trigger AS $$
BEGIN
    IF current_user != 'user1' THEN
        RAISE EXCEPTION 'You cannot delete records from this table!';
    END IF;  
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Upvotes: 6

Related Questions