sql - PostgreSQL 触发器根据条件添加/删除数据

Question

我正在寻求一个看似简单的部分的帮助，例如根据某些条件将数据插入另一个表中。所以重点是:有两个表:人员:

CREATE TABLE "public"."persons" ( 
    "id" Integer DEFAULT nextval('person_id_seq'::regclass) NOT NULL,
    "surname" Character Varying( 100 ) NOT NULL,
    "name" Character Varying( 100 ) NOT NULL,
    "patronymic" Character Varying( 100 ),
    "date_birth" Date NOT NULL,
    "place_birth" Character Varying( 1000 ) NOT NULL,
    "parent_id" Integer,
    "surnamepat" Character Varying,
    "surnamepat_short" Character Varying,
    PRIMARY KEY ( "id" ) );

法定代表人的历史:

CREATE TABLE "public"."history_legal_representatives" ( 
    "id" Integer DEFAULT nextval('history_legal_representative_id_seq'::regclass) NOT NULL,
    "person_id" Integer NOT NULL,
    "person_parent_id" Integer NOT NULL,
    "modified" Date DEFAULT ('now'::text)::date,
    PRIMARY KEY ( "id" ) );

在哪里:

"person_id" - persons (at table Persons is ID)
"person_parent_id" - legal representative (at table Persons is parent_id)

我正在尝试做的事情:

添加到persons表的记录时，没有指定parent_id，我们只添加这个条目，如果指定了parent_id，则添加 history_legal_representatives 记录:- person_id: = persons.id 和 person_parent_id: = persons.parent_id。

编辑表persons中的记录时，如果parent_id的值变为NULL，则从history_legal_representatives中删除这样一对。 (如果这样的逻辑不正确，请说明为什么\as更正确)

代码:

添加

CREATE TRIGGER addrep BEFORE INSERT ON "public"."persons" FOR EACH ROW EXECUTE PROCEDURE addrepresentative()[/SRC]

CREATE OR REPLACE FUNCTION public.addrepresentative()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    if NEW.parent_id != NULL then
    insert into history_legal_representatives (person_id, person_parent_id) 
    values (NEW.id, NEW.parent_id);
    end if;
    RETURN NEW;
END;
$function$

删除

CREATE TRIGGER delrep BEFORE UPDATE ON "public"."persons" FOR EACH ROW EXECUTE PROCEDURE delrepresentative()

CREATE OR REPLACE FUNCTION public.delrepresentative()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    if NEW.parend_id = null then
    delete from history_legal_representatives where id = NEW.id;
    end if;
    RETURN NEW;
END;
$function$

Answer 1

不起作用，因为 SQL 中的 NULL 比较具有不同的语法(+ delrepresentative 中的拼写错误)并且在删除函数中你想通过 person_id 删除

固定代码:

CREATE OR REPLACE FUNCTION public.addrepresentative()
  RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
    if NEW.parent_id IS NOT NULL then
    insert into history_legal_representatives (person_id, person_parent_id) 
    values (NEW.id, NEW.parent_id);
    end if;
    RETURN NEW;
END;
$function$

CREATE OR REPLACE FUNCTION public.delrepresentative()
  RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
    if NEW.parent_id IS null then
    delete from history_legal_representatives where person_id = NEW.id;
    end if;
    RETURN NEW;
END;
$function$

检查文档 https://www.postgresql.org/docs/9.6/static/sql-createtrigger.html ，我认为在这种情况下您需要 AFTER 触发器