Check column value before delete trigger postgreSQL

Question

Can someone tell me how can I check a specific column value before deleting from a table in postgreSQL?

My current code:

CREATE OR REPLACE FUNCTION f_tr_table_row_prohibit_delete() RETURNS TRIGGER AS 
$$
BEGIN
IF (OLD.table_state_code=0) THEN
    DELETE FROM mytable WHERE table_code=OLD.table_code;
ELSE
    RAISE EXCEPTION 'The deletion of a row is allowed only when table state code is 0!';
END IF;
END;
$$ 

CREATE TRIGGER tr_table_row_prohibit_delete 
BEFORE DELETE ON mytable 
FOR EACH ROW 
EXECUTE PROCEDURE f_tr_laua_rida_prohibit_delete();

LANGUAGE plpgsql SECURITY DEFINER STABLE
SET search_path = public, pg_temp;

I have a table named mytable with columns table_code and table_state_code. Before deleteing from, mytable I want to check if the table_state_code is 0, if not then it should not allow delete if it is anything else then it should delete the row.

With my current code, it correctly raises the exception when table_state_code is not 0 but when it is then it says : ERROR: DELETE is not allowed in a non-volatile function, instead of deleteing the row.

Thanks in advance.

Adam · Accepted Answer · 2017-12-17 20:44:12Z

1

In BEFORE DELETE trigger you can cancel the deletion or let it happen.

To cancel deletion you can return NULL or RAISE EXCEPTION.
To let the deletion to be continued return OLD.

The deletion has already started (as it is BEFORE DELETE trigger) so you don't have to execute DELETE FROM mytable WHERE table_code=OLD.table_code;.

See Overview of Trigger Behavior in the documentation.

edited Dec 17, 2017 at 20:44

answered Dec 17, 2017 at 20:38

Adam

5,6296 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check column value before delete trigger postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related