вставка триггера только в том случае, если тупель удовлетворяет условию

#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:

Итак, вам нужно триггерное решение, звучит как проблема с домашним заданием. Тогда возникает вопрос, хотите ли вы:

  1. прервать всю операцию
  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();