Ora-04072:无效触发器类型

Question

我正试图在Oracle11g上执行以下SQL语句。我对Oracle没有经验，也不知道为什么会失败。这个查询是由我们的开发人员提供给我的。

我试图通过OEM中的SQL工作表来执行此操作。

CREATE OR REPLACE TRIGGER TBL_ADMINCOMMAND_TRG BEFORE 
INSERT OR UPDATE ON tbl_AdminCommands FOR EACH ROW 
BEGIN 
IF inserting
AND :new.ADMINCOMMANDID IS NULL THEN
SELECT TBL_ADMINCOMMANDS_SEQ.nextval INTO :new.ADMINCOMMANDID FROM DUAL;
END IF;
END;
ALTER TRIGGER TBL_ADMINCOMMAND_TRG ENABLE;

Answer 1

您显示的代码对我有效，但仅作为两个单独的命令：

1(

CREATE OR REPLACE TRIGGER TBL_ADMINCOMMAND_TRG BEFORE 
INSERT OR UPDATE ON tbl_AdminCommands FOR EACH ROW 
BEGIN 
IF inserting
AND :new.ADMINCOMMANDID IS NULL THEN
SELECT TBL_ADMINCOMMANDS_SEQ.nextval INTO :new.ADMINCOMMANDID FROM DUAL;
END IF;
END;

2(

ALTER TRIGGER TBL_ADMINCOMMAND_TRG ENABLE;

试着一次做一个。

顺便说一句，这行：

SELECT TBL_ADMINCOMMANDS_SEQ.nextval INTO :new.ADMINCOMMANDID FROM DUAL;

可以在11G:中简化为

:new.ADMINCOMMANDID := TBL_ADMINCOMMANDS_SEQ.nextval;

事实上，整个触发器可以简化为：

CREATE OR REPLACE TRIGGER TBL_ADMINCOMMAND_TRG
BEFORE INSERT ON tbl_AdminCommands 
FOR EACH ROW 
WHEN (NEW.ADMINCOMMANDID IS NULL)
BEGIN 
    :new.ADMINCOMMANDID := TBL_ADMINCOMMANDS_SEQ.nextval;
END;

Answer 2

如果您使用的是SQL*Plus，您应该在PL/SQL命令的末尾单独使用一行正斜杠：

CREATE OR REPLACE TRIGGER TBL_ADMINCOMMAND_TRG
BEFORE INSERT OR UPDATE ON tbl_AdminCommands
FOR EACH ROW 
BEGIN 
  IF inserting AND :new.ADMINCOMMANDID IS NULL
  THEN
    SELECT TBL_ADMINCOMMANDS_SEQ.nextval
      INTO :new.ADMINCOMMANDID
      FROM DUAL;
  END IF;
END;
/
ALTER TRIGGER TBL_ADMINCOMMAND_TRG ENABLE;

还要注意，如果您的触发器使用IF inserting，则只能执行触发器BEFORE INSERT。