CODEWITHSUNDEEP
postgresqltriggers
J.Doe
J.Doe

Reputation: 93

Delete prevention trigger not working - postgresql

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();

Upvotes: 0

Views: 1182

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21346

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

Upvotes: 2

Related Questions