mysql - DbVisualizer 创建触发器时出错

Question

我使用的是 DbVisualizer Pro 9.5.6 版本，并且正在运行以下 sql 命令来创建触发器。

CREATE OR REPLACE TRIGGER "MYDB"."TG_T01_01" BEFORE INSERT ON T01
FOR EACH ROW
BEGIN
                :NEW.FECALT_01 := SYSDATE;
                :NEW.FECMOD_01 := SYSDATE;                  
                SELECT T01Q00.NEXTVAL INTO :NEW.ID_01 FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER "MYDB"."TG_T01_02" 
BEFORE UPDATE ON T01
FOR EACH ROW
BEGIN
                :NEW.FECMOD_01 := SYSDATE;              
END;
/...

它们是 BBDD 不同表的相同触发器列表。当我运行 sql 命令时，它会显示以下错误消息:

14:42:07 [CREATE - 0 rows, 0.032 secs] Command processed. No rows were affected

14:42:07 [:NEW.USUMOD_01 - 0 rows, 0.000 secs] [Code: 17439, SQL State: 99999] Tipo SQL no válido: sqlKind = UNINITIALIZED

14:42:07 [END - 0 rows, 0.000 secs] [Code: 900, SQL State: 42000] ORA-00900: sentencia SQL no válida

14:42:07 [/ - 0 rows, 0.000 secs] [Code: 900, SQL State: 42000] ORA-00900: sentencia SQL no válida

14:42:07 [:NEW.FECMOD_01 - 0 rows, 0.000 secs] [Code: 17439, SQL State: 99999] Tipo SQL no válido: sqlKind = UNINITIALIZED

14:42:07 [:NEW.USUALT_01 - 0 rows, 0.000 secs] [Code: 17439, SQL State: 99999] Tipo SQL no válido: sqlKind = UNINITIALIZED

14:42:07 [:NEW.USUMOD_01 - 0 rows, 0.000 secs] [Code: 17439, SQL State: 99999] Tipo SQL no válido: sqlKind = UNINITIALIZED

14:42:07 [SELECT - 0 rows, 0.000 secs] [Code: 1008, SQL State: 72000] ORA-01008: no todas las variables han sido enlazadas

14:42:07 [END - 0 rows, 0.015 secs] [Code: 900, SQL State: 42000] ORA-00900: sentencia SQL no válida

...

此后，将通过以下方式创建触发器:

CREATE OR REPLACE TRIGGER "MYDB"."TG_T01_01" BEFORE INSERT ON T01
    FOR EACH ROW
    BEGIN
                    :NEW.FECALT_01 := SYSDATE

注意:创建错误的trigger可以编辑并放置代码，trigger确实有效并且代码写得很好，所以我得出的结论是最有可能的是 DbVisualizer 的 sql 解释器将 ; 视为 END。

Answer 1

据我所知，这是错误的:

SELECT 01Q00.NEXTVAL INTO :NEW.ID_01 FROM DUAL;

因为它是一个无效的序列名称 - 应该以字母作为第一个字符，而不是数字。这是一个例子:

SQL> create table t01
  2    (id_01       number,
  3     fecalt_01   date,
  4     fecmod_01   date
  5    );

Table created.

SQL> create sequence 01q00;
create sequence 01q00
                *
ERROR at line 1:
ORA-02277: invalid sequence name

如果我们忽略错误，让我们创建一个触发器:

SQL> create or replace trigger tg_t01_01
  2  before insert on t01
  3  for each row
  4  begin
  5    :new.fecalt_01 := sysdate;
  6    :new.fecmod_01 := sysdate;
  7    select 01q00.nextval into :new.id_01 from dual;
  8  end;
  9  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER TG_T01_01:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
4/15     PL/SQL: ORA-00923: FROM keyword not found where expected

好的，让我们修复序列名称并重新创建触发器(使用新的序列名称):

SQL> create sequence seq01;

Sequence created.

SQL> create or replace trigger tg_t01_01
  2  before insert on t01
  3  for each row
  4  begin
  5    :new.fecalt_01 := sysdate;
  6    :new.fecmod_01 := sysdate;
  7    select seq01.nextval into :new.id_01 from dual;
  8  end;
  9  /

Trigger created.

SQL>

看起来还不错吧？