似乎无法为插入创建触发器 - Oracle

Question

我正在尝试在表上创建一个触发器以进行插入。以下是创建脚本：

CREATE OR REPLACE TRIGGER INTEG_QRY_NEW
AFTER INSERT
ON INTEG_LOG
FOR EACH ROW
DECLARE
VAR2 NUMBER(10);
LOG_TEXT1 VARCHAR2(1000);
RESULT1 VARCHAR2(1000);
QUERY_NUM1 VARCHAR2(1000);
QUERY_TIME1 VARCHAR2(1000);
LOG_INDEX1 NUMBER(10);
BEGIN
SELECT COUNT(*) INTO VAR2 FROM USER_TAB_COLS WHERE (COLUMN_NAME = 'RESULT' OR COLUMN_NAME = 'QUERY_NUM'  OR COLUMN_NAME = 'DATE_TIME' ) AND TABLE_NAME = 'INTEG_LOG';
IF VAR2=0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE INTEG_LOG ADD RESULT VARCHAR2(50); ALTER TABLE INTEG_LOG ADD DATE_TIME DATE; ALTER TABLE INTEG_LOG ADD QUERY_NUM VARCHAR2(50);';
END IF;
LOG_TEXT1 := :NEW.LOG_TEXT;
QUERY_TIME1 := :NEW.QUERY_TIME;
LOG_INDEX1 := :NEW.LOG_INDEX;
IF QUERY_TIME1 = '' THEN
RESULT1 := '-1';
QUERY_NUM1 := '';
UPDATE INTEG_LOG
SET RESULT = RESULT1,
DATE_TIME = CURRENT_DATE,
QUERY_NUM = QUERY_NUM1
WHERE LOG_INDEX = LOG_INDEX1;
ELSE
RESULT1 := SUBSTR(LOG_TEXT1,(INSTR(LOG_TEXT1,'Result = ')+9),9);
QUERY_NUM1 := SUBSTR(SUBSTR(LOG_TEXT1,INSTR(LOG_TEXT1,'Q# ')+3,20),1,INSTR(SUBSTR(LOG_TEXT1,INSTR(LOG_TEXT1,'Q# ')+3,20),' '));
UPDATE INTEG_LOG
SET RESULT = RESULT1,
DATE_TIME = CURRENT_DATE,
QUERY_NUM = QUERY_NUM1
WHERE LOG_INDEX = LOG_INDEX1;
END IF;
END;
/

1. 触发器负责处理以下列(来自应用程序(的常规插入：

   LOG_DATE号(10(，

   LOG_TIME数(10(，

   LOG_TYPE号(10(，

   LOG_TEXT VARCHAR2(2000字节(，

   LOG_INDEX数(10(，

   QUERY_TIME VARCHAR2(8 字节(

2. 触发器检查以下列是否存在(如果不存在，则添加它们(： 结果VARCHAR2(50字节(， DATE_TIME日期， QUERY_NUM VARCHAR2(50字节(

3. 现在它正在获取字符串(LOG_TEXT列(，并撤回结果值和查询编号("#Q"之后的内容(。
4. 接下来，它应该更新相同的插入操作，并将这些值添加到新的 3 列(结果、date_time 和 query_num(。

触发器确实已成功创建，但问题是，当我尝试插入表(INTEG_LOG(时，出现以下错误：

表字符串.字符串正在变异，触发器/函数可能看不到它

原因：触发器(或引用的用户定义的 plsql 函数( 在此语句中(尝试查看(或修改(一个表 在被触发它的语句修改的过程中。

操作：重写触发器(或函数(，使其不读取该触发器(或函数( 桌子。

这可能是语法错误吗？请帮忙。谢谢。

Answer 1

触发器编译的唯一原因是INTEG_LOG已经拥有动态SQL尝试添加的列。如果表没有这些列，触发器将不会添加它们，因为触发器将无效，因为这些 UPDATE 语句无法编译。

应该避免动态 SQL 的原因之一是它将编译错误转换为运行时错误。

但是，尝试在基于表构建的触发器中向表添加列是一个非常糟糕的主意。除此之外，DDL 语句问题隐式提交，我们不能在触发器中提交，因为这会对事务造成严重破坏。(是的，有pragma autonomous_transaction但使用它通常是一种代码气味(。

正确的解决方案是：

1. 编写一次性DDL 脚本以添加这些列
2. 如有必要，通过在执行 ALTER TABLE 语句之前检查这些列的先前存在，使脚本幂等
3. 运行脚本。
4. 用所有列填充表。

当然，这与由于这些 UPDATE 语句而导致的突变表错误无关。我们无法在托管触发器的表上执行 DML(包括选择(。我不确定您要尝试实现什么业务规则，但您需要一种不同的方法来执行此操作。