Create Trigger Before Deleting A Row with Condition Postgresql

Question

So for example I have this table

CREATE TABLE public."table" (
    c1 int4 NOT NULL,
    c2 int4 NOT NULL,
    c3 int4 NOT NULL,
    d varchar(20) NULL,
    e varchar(20) NULL,
    f date NULL
        
    CONSTRAINT table_pkey PRIMARY KEY (c1, c2, c3)
);

I want to add a restriction the delete only is possible when c1 and c2 and c3 are specified in other case don't allow the delete

I mean

delete from public."table" where c1=X 

throws ERROR!

delete from public."table" where c2=Y  

throws ERROR!

delete from public."table" where c3=Z 

throws ERROR!

delete from public."table" where c1=X and c2=Y 

throws ERROR!

delete from public."table" where c1=X and c3=Z 

throws ERROR!

and so on

only the delete is posible when

delete from public."table" where c1=X and c2=Y and c3=Z 

X,Y, Z are random values

I try

CREATE OR REPLACE FUNCTION verify_delete()
RETURNS TRIGGER AS $$
BEGIN

    IF OLD.c1 IS NULL OR OLD.c2 IS NULL OR OLD.c3 IS NULL THEN
        RAISE EXCEPTION 'ERROR!';
    END IF;


    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_verify_delete
BEFORE DELETE ON "table"
FOR EACH ROW
EXECUTE procedure verify_delete();

with no luck

any suggestions or ideas?

Answer 1

There is no way to implement your requirement. What you should do depends on what your real problem is.

If your problem is that a badly written interactive statement might inadvertently delete too many rows, you could solve the problem with a statement level trigger that throws an error if the transition table contains too many rows.