Delete prevention trigger not working - postgresql

Question

I created this trigger for a flight booking database where the trigger is meant to prevent reserved bookings from being deleted when using a delete sql statement. I have two statuses. One is 'r' for reserved and the other is 'c' for cancelled. My mission is to ensure that reserved bookings cannot be deleted and if a user tries to delete one, it raises the exception that's shown in my example trigger.

I have created this trigger and it has executed successfully but when I test it by deleting a reserved booking with status 'r' it runs and says "query returned successfully: one row affected..." which is not what I want. I want it to raise the exception and not delete the booking.

CREATE FUNCTION prevent_deletion() RETURNS trigger AS $prevent_deletion$

        BEGIN            
            IF 'status' = 'r' THEN
                RAISE EXCEPTION 'You cannot delete reserved bookings';
            END IF;
            RETURN NEW;

        END;
    $prevent_deletion$ LANGUAGE plpgsql;

    CREATE TRIGGER prevent_deletion AFTER DELETE ON flightbooking
        FOR EACH ROW EXECUTE PROCEDURE prevent_deletion();

@J.Doe: In a BEFORE trigger, the triggering operation is aborted if you return NULL, and NEW is NULL in your case (there's no "new" record for a DELETE). Try RETURN OLD instead. — Nick Barnes
– Nick Barnes, Commented Apr 10, 2017 at 15:26

Nick Barnes · Accepted Answer · 2017-04-10 15:21:56Z

2

This line:

IF 'status' = 'r' THEN

...compares the string 'status' to the string 'r' (and of course, they're never going to be equal...).

Comparing the field status to the string 'r' would look like this:

IF OLD.status = 'r' THEN

answered Apr 10, 2017 at 15:21

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

Delete prevention trigger not working - postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related