删除预防触发器不起作用-Postgresql [英] Delete prevention trigger not working - postgresql

问题描述

我为航班预订数据库创建了此触发器，该触发器旨在防止在使用delete sql语句时删除保留的预订。我有两种状态。一个是 r代表保留，另一个是 c代表取消。我的任务是确保保留的预订不会被删除，如果用户尝试删除预订，则会引发示例触发器中显示的异常。

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.

我创建了此触发器并成功执行了该触发器，但是当我通过删除状态为 r的保留预订进行测试时，它将运行并显示查询已成功返回：行受影响...，这不是我想要的。我希望它引发异常，而不是删除预订。

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

推荐答案

此行：

IF 'status' = 'r' THEN

...将字符串'status'与字符串'r'比较（当然，他们永远都不会平等...）。

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

比较字段 状态到字符串'r'如下：

IF OLD.status = 'r' THEN

这篇关于删除预防触发器不起作用-Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！