Want to find a trigger, found the table fields too much, I want to insert the data backup, don't want to write so many fields, so want to through the system table query out of the field to assemble, and found that this trigger can not work normally, is this why? "Whether" : new XXX after this form is in the string, perform when they make a mistake? Has been suggested not all variables have been bound, is not very understanding, excuse me what method can achieve the title says this automatic splicing method: new. (XXX)?

 create or replace the trigger "inspect_tasks 
"Before the delete or insert on the tasks 
For each row 
Declare 
V_sql varchar2 (2000); 
V_str varchar2 (2000); 

The begin 

If deleting then 
V_sql:='select' insert into tasks_cache (' | | wm_concat (tc olumn_name) | | ') values (' | | wm_concat (' decode (: old. '| | tc olumn_name | | ", null,' ' ' ' ' ' ' ', : old. '| | tc olumn_name | | ") ") | | "); 'the from user_tab_columns t where t.t able_name=upper (' tasks')'; 
Elsif: new task_id is not null then 
V_sql:='select' insert into tasks_cache (' | | wm_concat (tc olumn_name) | | ') values (' | | wm_concat (' decode (: new. '| | tc olumn_name | | ", null,' ' ' ' ' ' ' ', : new. '| | tc olumn_name | | ") ") | | "); 'the from user_tab_columns t where t.t able_name=upper (' tasks')'; 
end if; 

Dbms_output. Put_line (v_sql); 
The execute immediate v_sql into v_str; 
Dbms_output. Put_line (v_str); 
The execute immediate v_str; 

Exception when others then 
Dbms_output. Put_line (sqlerrm); 
Dbms_output. Put_line (v_str); 


end; 

CodePudding user response:

Dynamic joining together as if you have any question;
You alone out,

 declare vv_sql varchar2 (1000); 
The begin 
Vv_sql:='select' insert into tasks_cache (' | | wm_concat (tc olumn_name) | | ') values (' | | wm_concat (' decode (: new. '| | tc olumn_name | | ", null,' ' ' ' ' ' ' ', : new. '| | tc olumn_name | | ") ") | | "); 'the from user_tab_columns t where t.t able_name=upper (' tasks')'; 
Dbms_output. Put_line (vv_sql); 
end; 

is found,

CodePudding user response:

One-to-one stored in the backup tables, columns, isn't it better?