#sql #postgresql #triggers
Вопрос:
Я хочу создать триггер, который проверяет перед вставкой, соответствует ли тупель, который должен быть вставлен, определенному условию (которое также зависит от другой таблицы).
Например:
create trigger or replace check_tupel
before insert on A
for each row
execute
if exists (select x,y from B where B.x = A.x and B.y = A.y)
О, я использую PostgreSQL 13.
ПРАВКА: Да, я знаю, что могу сделать это без триггера, но я прошу решения с триггером по какой-то причине.
Я надеюсь, что есть способ сделать это… Моя другая идея состояла в том, чтобы создать UDF, который вызывается перед вставкой, но я не знаю, как проверить условие в этом UDF и вставить только в том случае, если функция возвращает значение true.
Комментарии:
1. Что ты будешь делать
if the tupel which is supposed to be inserted holds a specific condition
?2. Проверьте руководство, создайте функцию триггера (!), а затем триггер: postgresql.org/docs/current/plpgsql-trigger.html
3. Я хочу вставить тупель, если он соответствует условию.
Ответ №1:
Если вы просто хотите автоматически проверить запись перед вставкой ее в таблицу A
на основе таблицы B
с помощью определенной пользователем функции, вам вообще не нужен триггер. Подумайте о добавлении простого CHECK CONSTRAINT
:
CREATE TABLE a (
x int,
y int,
CONSTRAINT exists_in_b CHECK (NOT myfunc(x,y))
);
ДЕМОНСТРАЦИЯ: db<>fiddle
CREATE TABLE b (x int,
y int);
INSERT INTO b VALUES (42,42);
CREATE OR REPLACE FUNCTION myfunc(x int, y int)
RETURNS BOOLEAN AS $BODY$
SELECT EXISTS (SELECT 1 FROM b WHERE b.y =$1 AND b.x=$2 )
$BODY$
LANGUAGE sql;
CREATE TABLE a (
x int,
y int,
CONSTRAINT exists_in_b CHECK (NOT myfunc(x,y)) -- here the magic happens
);
Теперь, если мы попытаемся вставить значение, которое наша функция не проверяет, это вызовет исключение:
INSERT INTO a VALUES (42,42);
ERROR: new row for relation "a" violates check constraint "exists_in_b"
DETAIL: Failing row contains (42, 42).
SQL state: 23514
ИЗМЕНИТЬ (см. Комментарии): Решение с использованием триггера
CREATE OR REPLACE FUNCTION myfunc()
RETURNS TRIGGER AS $BODY$
BEGIN
IF EXISTS (SELECT 1 FROM b WHERE b.y =NEW.y AND b.x=NEW.x ) THEN
RAISE EXCEPTION 'tuple already exists in "b": % %', NEW.x,NEW.y;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER check_tupel
BEFORE INSERT OR UPDATE ON a
FOR EACH ROW EXECUTE PROCEDURE myfunc();
ДЕМОНСТРАЦИЯ: db<>fiddle
Комментарии:
1. это действительно хорошее решение, но я попросил его использовать триггер не просто так.
2. @maba, я вижу. Так что, возможно, моя последняя правка может вам помочь
Ответ №2:
Итак, вам нужно триггерное решение, звучит как проблема с домашним заданием. Тогда возникает вопрос, хотите ли вы:
- прервать всю операцию
- слегка раздавите строку, но продолжайте оставшуюся часть операции.
Следующее делает второе: (см. демонстрацию)
create or replace
function check_b_has_a()
returns trigger
language plpgsql
as $
begin
if exists
( select null
from b
where (b.x,b.y) =
(new.x, new.y)
)
then
return null;
else
return new;
end if;
end;
$;
create trigger a_bir
before insert
on a
for each row
execute function check_b_has_a();