创建的触发器有问题(Fro 主键序列)

Question

我创建了一个从 1 开始的序列，没有最大值我已经创建了一个自动插入主键的触发器，如下所示我还为主键必须是唯一且不为 null 的表设置了一个约束

create trigger MY_TEMP_TRIGGER
before insert on MY_TEMP
for each row
BEGIN
  SELECT MY_TEMP_SEQ.nextval 
    INTO :new.Id 
    FROM DUAL;
END;
/
INSERT INTO my_temp  
  (Id,Type, CreateDT, TypeId, TempType, DevType, Msg, File,User, Src, SrcDev)   
VALUES
 (MY_TEMP_SEQ.nextval,3434,2843,2453,2392,435,2390,'pension.txt','rereee',454545,3434)

结果：

第 1 行错误：
ORA-00001：唯一约束（用户。PK_MY_TEMP） 违反

表MY_TEMP已包含从 1 到 338 的 Id 字段

的值

那么，我应该如何在触发器和插入语句中处理这个问题。

Answer 1

如果您确实希望在插入时指定自己的 ID 值，并在其他时间使用该序列依赖触发器，那么您的触发器需要检查它是否传递了一个值 - 否则触发器生成的 ID 将优先（如果您在插入中指定MY_TEMP_SEQ.nextval，则该值将被跳过）。

BEGIN
    IF :NEW.id IS NULL THEN
        SELECT MY_TEMP_SEQ.nextval 
        INTO :NEW.id 
        FROM DUAL;
    END IF;
END;

处理已经存在的值更为复杂。如果您不打算传入自己的（非序列）ID 值，那么您可以将序列前滚到现有的最高值，例如：

ALTER SEQUENCE MY_TEMP_SEQ INCREMENT BY 338; -- or however many you need to skip
SELECT MY_TEMP_SEQ.nextval FROM DUAL;
ALTER SEQUENCE MY_TEMP_SEQ INCREMENT BY 1;

您将遇到问题的地方是，如果您尝试手动插入记录，指定 ID 值而不使用序列，该序列大于序列（例如使用 500）。当序列（最终）达到该值时，您仍将获得ORA-00001。

我认为您无法在触发器内处理这个问题或您的直接问题;我相信如果您尝试检查同一表中的现有值，您会收到一个变异表错误，而解决此问题只会增加复杂性（需要三个触发器）和潜在的不稳定。据我所知，处理这种情况的唯一简单方法是将插入包装在程序中并阻止直接插入，这可能也不是一个选项。但是，如果您将在不使用序列的情况下插入值，则只需担心这一点。

Answer 2

您可以在安装触发器之前递增序列：

declare 
  v_max_id my_temp.id%type;
  v_curr_seq  NUMBER;
begin
  select max(id) into v_max_id from my_temp;
  loop
    select MY_TEMP_SEQ.nextval into v_curr_seq from dual;
    exit when v_curr_seq >= v_max_id;
  end loop;
end;
/

Answer 3

您不能同时使用两者 - 只能使用其中之一。
在您的示例中，id值已作为序列的下一个值插入...这与触发器尝试使用的值相同。 我可能会把顺序倒过来，但结果是一样的。

如果要引用 INSERT 语句中的序列，则不需要触发器：

INSERT INTO my_temp  
  (Id,Type, CreateDT, TypeId, TempType, DevType, Msg, File,User, Src, SrcDev)   
VALUES
  (MY_TEMP_SEQ.nextval,3434,2843,2453,2392,435,2390,'pension.txt','rereee',454545,3434);

如果使用触发器

拥有触发器意味着您不能使用 INSERT 中的id列：

INSERT INTO my_temp  
  (Type, CreateDT, TypeId, TempType, DevType, Msg, File,User, Src, SrcDev)   
VALUES
  (3434, 2843, 2453, 2392, 435, 2390, 'pension.txt', 'rereee', 454545, 3434);

大多数人喜欢触发方法，因为它们习惯于MySQL AUTOINCREMENT或SQL Server的IDENTITY（Denali最终将支持ANSI序列）。