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

Question

我想创建一个触发器来记录表“osoba”中的以下更改(“登录”列中的更改)

我有这张 table “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

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

Answer 1

为什么要为 INSERT 使用 select？正如错误消息所示，没有表格就无法引用 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);

第二次选择也是如此