触发将行插入到日志表中的操作，该行将插入或删除已编译错误的人员表 [英] Trigger that inserts row into log table on insert or delete in a persons table compiled with errors

问题描述

我有一个带有名称和ID的人员表，以及一个具有who和what属性的日志表. 我想在删除或插入人员表时插入日志表.

I have a persons table with a name and id as well as a logs table with attributes who and what. I want to insert into the logs table when I delete or insert into the persons table.

这是我到目前为止所拥有的:

This is what I have so far:

CREATE OR REPLACE TRIGGER add_del
  BEFORE INSERT OR DELETE ON persons
  FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      INSERT INTO logs (who, what) VALUES (name, 'Insert into persons');
    ELSE
      INSERT INTO logs (who, what) VALUES (name, 'Delete from persons');
    END IF;
  END;
  /

为什么会编译并显示错误:

Why does this compile with the error:

警告:触发器因编译错误而创建.

Warning: Trigger created with compilation errors.

推荐答案

由于您没有发布错误，因此我不得不猜测.我的猜测是问题在于name在这种情况下不是有效的标识符.您需要引用:new.name或:old.name. :old.name在插入时将是NULL，而:new.name在删除时将是NULL，所以我假设您想要类似的东西

Since you didn't post the error, I have to guess. My guess is that the problem is that name is not a valid identifier in this context. You need to reference either :new.name or :old.name. :old.name will be NULL on an insert while :new.name will be NULL on a delete so I'm assuming you want something like

CREATE OR REPLACE TRIGGER add_del
  BEFORE INSERT OR DELETE ON persons
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO logs (who, what) VALUES (:new.name, 'Insert into persons');
  ELSE
    INSERT INTO logs (who, what) VALUES (:old.name, 'Delete from persons');
  END IF;
END;

这篇关于触发将行插入到日志表中的操作，该行将插入或删除已编译错误的人员表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！