postgresql 按要求触发

lg40wkob 于 2022-11-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(100)

我得到了两个表，一个是People表，另一个是vehicle表，vehicle表是一个外键，名为Owner（代码中的proprietario），引用了people.id（代码中的p.id）
我想创建一个触发器，当一个人（在表pessoa）有5辆车，插入或更新将被忽略。查询工作。触发器不真的。

CREATE FUNCTION trigger_owner() RETURNS trigger as $trigger_owner$
BEGIN
     IF (SELECT  count(p.id) as NoCars
        FROM pessoa p , veiculo v 
        WHERE p.id = v.proprietario and v.proprietario  = NEW.proprietario
        GROUP BY p.id) 
     >= 5
        RAISE EXCEPTION '% owner cant have more than 20 vehicles', NEW.proprietario;
    END IF
END;

$trigger_owner$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_owner BEFORE INSERT OR UPDATE ON veiculo FOR EACH ROW EXECUTE FUNCTION trigger_owner();
运行时出错：错误：SQL表达式的结尾缺少“THEN”
我可以添加一个Then，但不确定我必须放置哪些命令，以便忽略插入/更新

postgresql

来源：https://stackoverflow.com/questions/74576736/trigger-with-requirements

1条答案

按热度按时间

2izufjch1#

CREATE OR REPLACE FUNCTION trigger_owner() RETURNS trigger as $trigger_owner$
BEGIN
     IF (SELECT  count(p.id) as NoCars
        FROM pessoa p , veiculo v 
        WHERE p.id = v.proprietario and v.proprietario  = NEW.proprietario
        GROUP BY p.id) 
     >= 20
         THEN RAISE NOTICE 'id % owner cant have more than 20 vehicles', NEW.proprietario;
        RETURN NULL;
    END IF;
   RETURN NEW;
   
END;
$trigger_owner$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER trigger_owner BEFORE INSERT OR UPDATE ON veiculo
FOR EACH ROW EXECUTE FUNCTION trigger_owner();

赞(0）回复(0）举报 2022-11-29

我来回答

postgresql 按要求触发

1条答案

相关问题

热门标签

最新问答