CODEWITHSUNDEEP
sqlpostgresqltriggers
Tarvo Erimäe
Tarvo Erimäe

Reputation: 3

Check column value before delete trigger postgreSQL

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.

Upvotes: 0

Views: 1803

Answers (1)

Adam
Adam

Reputation: 5599

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.

Upvotes: 1

Related Questions