sql - 此查询触发器有什么问题？

我想创建一个触发器，记录表“osoba”（在“log in”列中的更改）中的以下更改
我有这张桌子“osoba”：

CREATE TABLE Osoba (
  id_osoba SERIAL NOT NULL UNIQUE,
  login VARCHAR,
  jmeno VARCHAR,
  prijmeni VARCHAR,
  heslo_hash VARCHAR,
)

还有这个记录表

    CREATE TABLE osoba_zmeny(
    operace         char(1)   NOT NULL,
    cas_otisk       timestamp NOT NULL,
    id_osoba        integer   NOT NULL,
    login           text      NOT NULL,
);

具有以下功能：

CREATE OR REPLACE FUNCTION osoba_zmeny() RETURNS TRIGGER AS $osoba_zmeny$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO osoba_zmeny SELECT 'D', now(), id_osoba, OLD.login;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO osoba_zmeny SELECT 'U', now(), id_osoba, NEW.login;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO osoba_zmeny SELECT 'I', now(), id_osoba, NEW.login;
            RETURN NEW;
        END IF;
        RETURN NULL;
    END;
$osoba_zmeny$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS osoba_zmeny on osoba;
CREATE TRIGGER osoba_zmeny
AFTER INSERT OR UPDATE OR DELETE ON osoba
    FOR EACH ROW EXECUTE PROCEDURE osoba_zmeny();

CREATE TRIGGER-查询成功返回，但如果要将记录插入表“Osoba”，则返回：

INSERT INTO public.osoba(
    id_osoba, login, jmeno, prijmeni, heslo_hash)
    VALUES (13, 'test_login', 'test_jmeno', 'test_prijmeni', 'test_heslo_hash');

    ERROR:  column "id_osoba" does not exist
LINE 1: INSERT INTO osoba_zmeny SELECT 'I', now(), id_osoba, NEW.log...
                                                   ^
HINT:  There is a column named "id_osoba" in table "osoba_zmeny", but it cannot be referenced from this part of the query.
QUERY:  INSERT INTO osoba_zmeny SELECT 'I', now(), id_osoba, NEW.login
CONTEXT:  PL/pgSQL function osoba_zmeny() line 10 at SQL statement
SQL state: 42703

你知道我做错了什么吗？谢谢你的帮助

最佳答案

为什么要用select来表示INSERT？正如错误消息所示，id_osoba不能在没有表的情况下被引用。根据触发器的类型，您需要从NEW或OLD记录中获取该值，例如：

INSERT INTO osoba_zmeny SELECT 'D', now(), id_osoba, OLD.login;

应该是

INSERT INTO osoba_zmeny values ('D', now(), old.id_osoba, OLD.login);

第二次选择相同