如何在trigger 中执行动态sql

Question

CREATE OR REPLACE TRIGGER TRG_TEMP
BEFORE UPDATE OR INSERT OR DELETE ON TEMP
FOR EACH ROWDECLARE 
 execSQL varchar(8000);
 FIELD_NAME varchar2(100);
 oldField varchar(500);
 newField varchar(500); 
BEGIN  
   IF INSERTING OR UPDATING THEN
 
         FIELD_NAME := 'NAME';
         oldField := :old.NAME; 
         
         execSQL := 'INSERT into audit_log_detail(ID,FORM_TYPE,REF_NO,FIELD_NAME,FIELD_TYPE,ACTION,
                    FROM_VALUE,TO_VALUE,LAST_UPDATE_DATE,LAST_UPDATE_BY)
                    values(SEQ_AUDIT_LOG_DTL.NEXTVAL,
                    ''TYPE '',''REQ_NO'',''NAME'',''String'',''U'', ''abcd''  
                    , :new.' || FIELD_NAME || ' , SYSDATE ,''XRAY_TEST'' )' ;
                dbms_output.put_line (execSQL);
 
        EXECUTE immediate (  execSQL );
 
   END IF;  -- IF DELETING THEN
  --   INSERT THE DELETE AUDIT LOG
  -- END IF;
end TRG_TEMP;以上代码 :new.' || FIELD_NAME 的用意是想根据 FIELD_NAME 来动态地记录想要记录的字段 。temp 表结构很简单：
create table TEMP
(
  ID   NUMBER,
  NAME VARCHAR2(100)
)如上， 我执行以下sql：
INSERT INTO TEMP VALUES('1', 'ABC');可列印 如下sql：
INSERT into audit_log_detail(ID,FORM_TYPE,REF_NO,FIELD_NAME,FIELD_TYPE,ACTION,
                    FROM_VALUE,TO_VALUE,LAST_UPDATE_DATE,LAST_UPDATE_BY)
                    values(SEQ_AUDIT_LOG_DTL.NEXTVAL,
                    'TYPE ','REQ_NO','NAME','String','U', 'abcd'  
                    , :new.NAME , SYSDATE ,'XRAY_TEST' )然而这句话执行  EXECUTE immediate (  execSQL ); 报如下错误：ORA-01008: 并非所有变量都已绑定
ORA-06512: 在 "DPAS.TRG_TEMP", line 35
ORA-04088: 触发器 'DPAS.TRG_TEMP' 执行过程中出错请各位大大给点意见，应该怎样实现此功能。

Answer 1

INSERT into audit_log_detail(ID,FORM_TYPE,REF_NO,FIELD_NAME,FIELD_TYPE,ACTION, 
                    FROM_VALUE,TO_VALUE,LAST_UPDATE_DATE,LAST_UPDATE_BY) 
                    values(SEQ_AUDIT_LOG_DTL.NEXTVAL, 
                    'TYPE ','REQ_NO','NAME','String','U', 'abcd'  
                    , :new.NAME , SYSDATE ,'XRAY_TEST' ) 这里主要是 :new.NAME 不对，应该把值拼写到sql里或者用using

Answer 2

不:new.name的值拼到sql的语句里或者
把sql里的:new.NAME换成:1
然后用using
execute immediate execSQL using :new.NAME; 

Answer 3

动态sql里， 带 “:”的被标识为绑定变量，整个值是需要在执行时带 using 的你这里，可以尝试将 :new.name 使用 || 连接进去，而不要直接在 '' 的字符串中

Answer 4

==================================
用替换变量。修改红色部分：
1、  :x
2、EXECUTE immediate (  execSQL ) USING :new.NAME;

Answer 5

 execSQL := 'INSERT into audit_log_detail(ID,FORM_TYPE,REF_NO,FIELD_NAME,FIELD_TYPE,ACTION, 
                    FROM_VALUE,TO_VALUE,LAST_UPDATE_DATE,LAST_UPDATE_BY) 
                    values(SEQ_AUDIT_LOG_DTL.NEXTVAL, 
                    ''TYPE '',''REQ_NO'',''NAME'',''String'',''U'', ''abcd''  
                    , :new.' || FIELD_NAME || ' , SYSDATE ,''XRAY_TEST'' )' ; 谢谢各位的回复，  我用 FIELD_NAME   在这里做连接 是想可以通过传入不同的FIELD_NAME 来备份不同的字段。

Answer 6

INSERT into audit_log_detail(ID,FORM_TYPE,REF_NO,FIELD_NAME,FIELD_TYPE,ACTION, 
                    FROM_VALUE,TO_VALUE,LAST_UPDATE_DATE,LAST_UPDATE_BY) 
                    values(SEQ_AUDIT_LOG_DTL.NEXTVAL, 
                    'TYPE ','REQ_NO','NAME','String','U', 'abcd'  
                    , :new.NAME , SYSDATE ,'XRAY_TEST' ) 其实这条语句直接写在 trigger 中是可以顺利执行的。

Answer 7

既然触发器建在一个表上，:new后面跟的字段的可能选择是确定的
有个表配置需要做记录的字段名
加个游标对该表进行遍历，取出字段名
:new.' || FIELD_NAME || '改成:1
进行判断
for cur1 in (select .... from ..)loop
  if cur1.name=' XXX' then
  execute immediate excesql using :new.XXX;
  else if cur1.name='XX1' then
  execute............ using :new.XX2;
 ...
  end if;
end loop;如果没有特殊需要，试着将触发器改成表级的，行级触发器每条记录都进行一次这样的操作效率可能会太低

Answer 8

表级锁，用不了:new你这里是trigger，所以你已经很明确里面的字段了。不必要用动态sql执行了叻。

Answer 9

就是不明确字段，字段需要读取配置才知道的。
 wildwave  的比较符合此需求和。