001: /*
002:
003: Derby - Class org.apache.derby.impl.tools.dblook.DB_Trigger
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to you under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derby.impl.tools.dblook;
023:
024: import java.sql.Connection;
025: import java.sql.Statement;
026: import java.sql.PreparedStatement;
027: import java.sql.ResultSet;
028: import java.sql.SQLException;
029:
030: import java.util.HashMap;
031: import java.util.StringTokenizer;
032:
033: import org.apache.derby.tools.dblook;
034:
035: public class DB_Trigger {
036:
037: /* ************************************************
038: * Generate the DDL for all triggers in a given
039: * database.
040: * @param conn Connection to the source database.
041: * @return The DDL for the triggers has been written
042: * to output via Logs.java.
043: ****/
044:
045: public static void doTriggers(Connection conn) throws SQLException {
046:
047: Statement stmt = conn.createStatement();
048: ResultSet rs = stmt
049: .executeQuery("SELECT TRIGGERNAME, SCHEMAID, "
050: + "EVENT, FIRINGTIME, TYPE, TABLEID, REFERENCEDCOLUMNS, "
051: + "TRIGGERDEFINITION, REFERENCINGOLD, REFERENCINGNEW, OLDREFERENCINGNAME, "
052: + "NEWREFERENCINGNAME FROM SYS.SYSTRIGGERS WHERE STATE != 'D'");
053:
054: boolean firstTime = true;
055: while (rs.next()) {
056:
057: String trigName = dblook.addQuotes(dblook
058: .expandDoubleQuotes(rs.getString(1)));
059: String trigSchema = dblook.lookupSchemaId(rs.getString(2));
060:
061: if (dblook.isIgnorableSchema(trigSchema))
062: continue;
063:
064: trigName = trigSchema + "." + trigName;
065: String tableName = dblook.lookupTableId(rs.getString(6));
066:
067: // We'll write the DDL for this trigger if either 1) it is on
068: // a table in the user-specified list, OR 2) the trigger text
069: // contains a reference to a table in the user-specified list.
070:
071: if (!dblook.stringContainsTargetTable(rs.getString(8))
072: && (dblook.isExcludedTable(tableName)))
073: continue;
074:
075: if (firstTime) {
076: Logs
077: .reportString("----------------------------------------------");
078: Logs.reportMessage("DBLOOK_TriggersHeader");
079: Logs
080: .reportString("----------------------------------------------\n");
081: }
082:
083: String createTrigString = createTrigger(trigName,
084: tableName, rs);
085:
086: Logs.writeToNewDDL(createTrigString);
087: Logs.writeStmtEndToNewDDL();
088: Logs.writeNewlineToNewDDL();
089: firstTime = false;
090:
091: }
092:
093: rs.close();
094: stmt.close();
095:
096: }
097:
098: /* ************************************************
099: * Generate DDL for a specific trigger.
100: * @param trigName Name of the trigger.
101: * @param tableName Name of the table on which the trigger
102: * fires.
103: * @param aTrig Information about the trigger.
104: * @return The DDL for the current trigger is returned
105: * as a String.
106: ****/
107:
108: private static String createTrigger(String trigName,
109: String tableName, ResultSet aTrig) throws SQLException {
110:
111: StringBuffer sb = new StringBuffer("CREATE TRIGGER ");
112: sb.append(trigName);
113:
114: // Firing time.
115: if (aTrig.getString(4).charAt(0) == 'A')
116: sb.append(" AFTER ");
117: else
118: sb.append(" NO CASCADE BEFORE ");
119:
120: // Event.
121: switch (aTrig.getString(3).charAt(0)) {
122: case 'I':
123: sb.append("INSERT");
124: break;
125: case 'D':
126: sb.append("DELETE");
127: break;
128: case 'U':
129: sb.append("UPDATE");
130: String updateCols = aTrig.getString(7);
131: if (!aTrig.wasNull()) {
132: sb.append(" OF ");
133: sb.append(dblook.getColumnListFromDescription(aTrig
134: .getString(6), updateCols));
135: }
136: break;
137: default: // shouldn't happen.
138: Logs.debug("INTERNAL ERROR: unexpected trigger event: "
139: + aTrig.getString(3), (String) null);
140: break;
141: }
142:
143: // On table...
144: sb.append(" ON ");
145: sb.append(tableName);
146:
147: // Referencing...
148: char trigType = aTrig.getString(5).charAt(0);
149: String oldReferencing = aTrig.getString(11);
150: String newReferencing = aTrig.getString(12);
151: if ((oldReferencing != null) || (newReferencing != null)) {
152: sb.append(" REFERENCING");
153: if (aTrig.getBoolean(9)) {
154: sb.append(" OLD");
155: if (trigType == 'S')
156: // Statement triggers work on tables.
157: sb.append("_TABLE AS ");
158: else
159: // don't include "ROW" keyword (DB2 doesn't).
160: sb.append(" AS ");
161: sb.append(oldReferencing);
162: }
163: if (aTrig.getBoolean(10)) {
164: sb.append(" NEW");
165: if (trigType == 'S')
166: // Statement triggers work on tables.
167: sb.append("_TABLE AS ");
168: else
169: // don't include "ROW" keyword (DB2 doesn't).
170: sb.append(" AS ");
171: sb.append(newReferencing);
172: }
173: }
174:
175: // Trigger type (row/statement).
176: sb.append(" FOR EACH ");
177: if (trigType == 'S')
178: sb.append("STATEMENT ");
179: else
180: sb.append("ROW ");
181:
182: // DB2 requires the following keywords in order to work.
183: sb.append("MODE DB2SQL ");
184:
185: // Finally, the trigger action.
186: sb.append(dblook.removeNewlines(aTrig.getString(8)));
187: return sb.toString();
188:
189: }
190:
191: }
|