从另外一个地方导出来的表,再导入的时候,存在百把几十个触发器,用PL/SQL删老是报触发器不存在的异常,但是呢,用SQL查能查出来,删也能删掉。
决定用JDBC的循环来删这些。。。。
1 public static void deleteTriggers() throws SQLException { 2 Connection connection = getConnection(); 3 4 List<String> triggerNameList = new ArrayList<String>(); 5 String sql = "SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER='NJZJLMS'";//OWNER后的一定要大写,否则查不到东西 6 Statement st = connection.createStatement(); 7 Statement st2 = connection.createStatement(); 8 ResultSet rs = st.executeQuery(sql); 9 while (rs.next()) { 10 String triggerName = rs.getString("TRIGGER_NAME"); 11 if (!triggerName.contains("=")) { 12 triggerNameList.add(triggerName); 13 } 14 } 15 System.out.println("triggerNameList:" + triggerNameList.size()); 16 String sql2 = ""; 17 for (int i = 0; i < triggerNameList.size(); i++) { 18 System.out.println("triggerName:"+triggerNameList.get(i)); 19 } 20 for (int i = 0; i < triggerNameList.size(); i++) { 21 sql2 = "drop trigger \"" + triggerNameList.get(i) + "\"";//这里triggername一定要用双引号,单引号无效 22 System.out.println("sql2:"+sql2); 23 st2.execute(sql2); 24 } 25 st.close(); 26 rs.close(); 27 connection.close(); 28 }