CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.1
Marcus Junius Brutus
Marcus Junius Brutus

Reputation: 27286

trigger to silently preempt insertion in a table

Is it possible for a trigger to be defined in such a way that the row that was to be inserted is not inserted, without raising an exception? My use case is that I want to simplify the exception handling for the client library: the client library will just execute a statement to insert a row in a table and I was hoping that the trigger could be defined, more or less using the below syntax:

CREATE TRIGGER control_tr AFTER INSERT ON tableFoo
FOR EACH ROW
EXECUTE PROCEDURE control_tr_fun();

CREATE OR REPLACE FUNCTION control_tr_fun() RETURNS TRIGGER AS $$
BEGIN
    IF (NOT condition_is_met(NEW.a, NEW.b, NEW.c)) THEN
        DO NOTHING INSTEAD OF INSERT // syntax I am hoping for instead of RAISE EXCEPTION
    ELSE
        RETURN NEW;
    END IF;
END
$$ LANGUAGE plpgsql;

I appreciate that I can ask the client library to call a PL/pgSQL function or make a trigger that RAISEs an exception and ask the client library to catch the exception (if raised) and just ignore it, but I am looking for a way to implement this as transparently for the client as possible.

Upvotes: 0

Views: 166

Answers (1)

Kouber Saparev
Kouber Saparev

Reputation: 8105

If you RETURN NULL then nothing will happen, the INSERT will fail silently. But you need to define the trigger as BEFORE INSERT, and not AFTER INSERT as it is in your example.

If you RAISE EXCEPTION, then the entire transaction will fail.

You can also RAISE NOTICE without failing the transaction and catch it in the client library, but only if it was the last notice produced. You cannot stack notices.

Upvotes: 2

Related Questions