时间戳

首页 > 解决方案 > PLSQL - 创建触发器抛出错误

plsql - PLSQL - 创建触发器抛出错误

问题描述

我有一个需要为其生成触发器的表。下面是我的代码：

CREATE OR REPLACE EDITIONABLE TRIGGER "MYUSER"."REGISTRATION_TRG" BEFORE
  INSERT ON REGISTRATIONS FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF INSERTING
  AND :NEW.REGISTRATIONID                              IS NULL THEN
  SELECT REGISTRATIONS_SEQ.NEXTVAL
  INTO :NEW.REGISTRATIONID
  FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;

当我运行它时，我收到以下错误：

Error starting at line 0 in command:
CREATE OR REPLACE EDITIONABLE TRIGGER "MYUSER"."REGISTRATIONS_TRG" BEFORE
  INSERT ON REGISTRATIONS FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF INSERTING
  AND :NEW.REGISTRATIONID                              IS NULL THEN
  SELECT REGISTRATIONS_SEQ.NEXTVAL
  INTO :NEW.REGISTRATIONID
  FROM SYS.DUAL
Error report:
SQL Command: CREATE OR REPLACE EDITIONABLE
Failed: Warning: execution completed with warning

Error starting at line 7 in command:
END IF
Error report:
Unknown Command

Error starting at line 8 in command:
END COLUMN_SEQUENCES
Error report:
Unknown Command

Error starting at line 9 in command:
END
Error report:
Unknown Command

我究竟做错了什么？

谢谢！

标签： plsqltriggers

解决方案

首先，它适用于 SQL*Plus 和 PL/SQL Developer：

create table registrations(registrationid integer);

create sequence registrations_seq;

CREATE OR REPLACE EDITIONABLE TRIGGER "REGISTRATION_TRG" BEFORE
  INSERT ON REGISTRATIONS FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF INSERTING
  AND :NEW.REGISTRATIONID                              IS NULL THEN
  SELECT REGISTRATIONS_SEQ.NEXTVAL
  INTO :NEW.REGISTRATIONID
  FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/

（我删除"MYUSER"是因为我没有使用该名称的用户帐户。）

但是，您可以稍微简化一下。首先，用任何语言整齐地布置代码是值得的，所以第一步是：

create or replace editionable trigger registration_trg
    before insert on registrations
    for each row
begin
    <<column_sequences>>
    begin
        if inserting and :new.registrationid is null then
            select registrations_seq.nextval into :new.registrationid
            from sys.dual;
        end if;
    end column_sequences;
end;

然后，

  1. 如果它是唯一的块，则不需要命名块。
  2. 这是一个插入触发器，因此您无需测试if inserting.
  3. 在同一列中允许生成的值和用户输入的值是灾难的秘诀，但如果必须，您可以将其定义为when触发器规范中的条件。
  4. PL/SQL 语言有一个赋值运算符:=，因此您无需查询即可为变量赋值。

那么为什么不简单地：

create or replace trigger registration_trg
    before insert on registrations
    for each row
    when (new.registrationid is null)
begin
    :new.registrationid := registrations_seq.nextval;
end;

更简单地说，从 Oracle 12.1 开始，您甚至不需要触发器：

drop table registrations;

create table registrations (id integer generated always as identity);

insert into registrations values (default);

select * from registrations;

        ID
----------
         1

推荐阅读