database – 查询在触发后没有结果数据的目标

发布时间：2020-12-13 16:09:50 所属栏目：百科来源：网络整理

导读：我的触发器有问题.在插入新行时,它将检查文章是否未售出.我可以在软件中做到这一点,但我认为这对DB来说更好. -- Create functionCREATE OR REPLACE FUNCTION checkSold() RETURNS TRIGGER AS $checkSold$ BEGIN SELECT offer_id FROM offer WHERE offer_id =

我的触发器有问题.在插入新行时,它将检查文章是否未售出.我可以在软件中做到这一点,但我认为这对DB来说更好.

-- Create function
CREATE OR REPLACE FUNCTION checkSold() RETURNS TRIGGER AS $checkSold$
    BEGIN
        SELECT offer_id FROM offer WHERE offer_id = NEW.offer_id AND date_sale IS NULL;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'The Offer is Sold!';
    END IF;
    RETURN NEW;
END;
$checkSold$LANGUAGE plpgsql;


-- Create trigger
Drop TRIGGER checkSold ON tag_map;
CREATE TRIGGER checkSold BEFORE INSERT ON tag_map FOR EACH ROW EXECUTE PROCEDURE checkSold();

INSERT INTO tag_map (tag_id,offer_id) VALUES (824,80);

这是插入后的错误.

[WARNING  ] INSERT INTO tag_map (tag_id,80)
            ERROR:  query has no destination for result data
            HINT:  If you want to discard the results of a SELECT,use PERFORM instead.
            CONTEXT:  PL/pgSQL function "checksold" line 2 at SQL statement

触发器有什么问题？没有它效果很好.

解决方法

更换

SELECT offer_id FROM offer WHERE offer_id = NEW.offer_id AND date_sale IS NULL;

同

PERFORM offer_id FROM offer WHERE offer_id = NEW.offer_id AND date_sale IS NULL;

如建议.

手册中的更多信息(“38.5.2. Executing a Command With No Result”).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!