sql - 如何在更新触发器之前获取内部实体的 ID？

Question

我有这个架构：

每一种动物都应该生活在它的世界范围内。当我尝试更新动物的 x 和 y 坐标时，它们应该在只有最新坐标仍在世界部分区域内时更新，或者如果它们在另一个世界部分内则返回 null（这在我的架构的上下文中是不正确的） .

我有一个工作正常的更新功能：

create or replace function update_animal_location(id integer, x_arg integer, y_arg integer)
returns table (
                animals_id integer,
                animals_sort_id integer,
                name text,
                x integer,
                y integer
) as
$$
    begin
        update animals set x = x_arg, y = y_arg where animals.animals_id = id;
        return query select * from animals where animals.animals_id = id;
    end;
$$ language plpgsql;

然后我需要使用触发器以某种方式验证此更新。我不明白的是如何引用具有我要检查的新位置的实体的 id。

这是我尝试做的事情：

create or replace function validate_animal_location()
    returns trigger as
$$
declare
    -- Get coordinates (corners) of a world part where an exact animal lives
    location_x1 integer := (select x1
        from world_part
        inner join animals_location on world_part.world_part_id = animals_location.world_part_id
        inner join animals on animals.animals_sort_id = animals_location.animals_sort_id
    where animals.animals_id = old.animals_id);
    location_x2 integer := (select x2
        from world_part
        inner join animals_location on world_part.world_part_id = animals_location.world_part_id
        inner join animals on animals.animals_sort_id = animals_location.animals_sort_id
    where animals.animals_id = old.animals_id);
    location_y1 integer := (select y1
        from world_part
        inner join animals_location on world_part.world_part_id = animals_location.world_part_id
        inner join animals on animals.animals_sort_id = animals_location.animals_sort_id
    where animals.animals_id = old.animals_id);
    location_y2 integer := (select y2
        from world_part
        inner join animals_location on world_part.world_part_id = animals_location.world_part_id
        inner join animals on animals.animals_sort_id = animals_location.animals_sort_id
    where animals.animals_id = old.animals_id);

    -- Get new coordinates of an exact animal
    x_new integer := (select x from animals where animals.animals_id = new.animals_id);
    y_new integer := (select y from animals where animals.animals_id = new.animals_id);

begin
    if x_new < location_x1 or x_new > location_x2 or y_new < location_y1 or y_new > location_y2 then
        return null;
    end if;
    return new;
end;
$$ language plpgsql;

create trigger validate_animal_location
    before update or insert
    on animals for each row execute procedure validate_animal_location();

我向您保证，这也可以正常工作，但只是一个查询本身：

location_x1 integer := (select x1
        from world_part
        inner join animals_location on world_part.world_part_id = animals_location.world_part_id
        inner join animals on animals.animals_sort_id = animals_location.animals_sort_id
    where animals.animals_id = 1); -- if I set some valid id number here,
    -- it works as intended and returns x1 coordinate of the world part the animal should be in.

但我想在触发器内完成这项工作。

标签： sqldatabasetriggers