cusorcreatetigger.txt

用游标创建触发器

declare
       table_name varchar2(100); 
       str varchar2(500);
  
       cursor mycur is SELECT table_name1 from
              (select 'Com_Shipping' table_name1 FROM dual
              union
              select 'Str_Shipping' table_name1 from dual);
begin
       open mycur;
       loop
       fetch mycur into table_name;
             exit when mycur%notfound;
             dbms_output.put_line(table_name||'================');
             
             --创建触发器
             str := 'create or replace trigger TRI_'||table_name ||chr(13)||
             ' AFTER insert or update or delete on '||table_name ||chr(13)||
             ' declare'||
             ' str_value varchar2(2);'||
             ' begin'||
             ' if inserting then'||
             ' str_value := ''I'';'||
             ' elsif updating then '||
             ' str_value := ''U'';'||
             ' elsif deleting then '||
             ' str_value := ''D'';'||
             ' end if;'||
             ' end;';
             dbms_output.put_line(str);
             execute immediate str; 
       end loop;
       close mycur;
end;