Java > Open Source Codes > org > hsqldb > sample > TriggerSample


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30
31
32
33 // use the org.hsqldb.sample package here; but will be in your own package usually
34 package org.hsqldb.sample;
35
36 import java.io.PrintWriter ;
37 import java.sql.Connection ;
38 import java.sql.DriverManager ;
39 import java.sql.PreparedStatement ;
40 import java.sql.ResultSet ;
41 import java.sql.ResultSetMetaData ;
42 import java.sql.SQLException ;
43 import java.sql.Statement ;
44
45 import org.hsqldb.Trace;
46 import org.hsqldb.Trigger;
47 import org.hsqldb.lib.StringUtil;
48
49 // peterhudson@users 20020130 - patch 478657 by peterhudson - new class
50 // fredt@users 20030727 - signature altered
51 // boucherb@users 20040315 - sample updated
52
53 /**
54  * <P>Sample code for use of triggers in hsqldb.
55  *
56  * SQL to invoke is:<p>
57  * CREATE TRIGGER triggerSample BEFORE|AFTER INSERT|UPDATE|DELETE
58  * ON myTable [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL "myPackage.trigClass"<br>
59  *
60  * This will create a thread that will wait for its firing event to occur;
61  * when this happens, the trigger's thread runs the 'trigClass.fire'
62  * Note that this is still in the same Java Virtual Machine as the
63  * database, so make sure the fired method does not hang.<p>
64  *
65  * There is a queue of events waiting to be run by each trigger thread.
66  * This is particularly useful for 'FOR EACH ROW' triggers, when a large
67  * number of trigger events occur in rapid succession, without the trigger
68  * thread getting a chance to run. If the queue becomes full, subsequent
69  * additions to it cause the database engine to suspend awaiting space
70  * in the queue. Take great care to avoid this situation if the trigger
71  * action involves accessing the database, as deadlock will occur.
72  * This can be avoided either by ensuring the QUEUE parameter makes a large
73  * enough queue, or by using the NOWAIT parameter, which causes a new
74  * trigger event to overwrite the most recent event in the queue.
75  * The default queue size is 1024.<p>
76  *
77  * Ensure that "myPackage.trigClass" is present in the classpath which
78  * you use to start hsql.<p>
79  *
80  * If the method wants to access the database, it must establish
81  * a JDBC connection.<p>
82  *
83  * When the 'fire' method is called, it is passed the following arguments: <p>
84  *
85  * fire (int type, String trigName, String tabName, Object oldRow[],
86  * Object[] newRow) <p>
87  *
88  * where 'type' is one of the values enumerated in the Trigger interface and
89  * the 'oldRow'/'newRow' pair represents the rows acted on. The first
90  * length - 1 array slots contain column values and the final slot contains
91  * either null or the value of the internally assigned row identity, if
92  * the concerned table has no primary key. The final slot must _never_ be
93  * modified. <p>
94  *
95  * The mapping of row classes to database types is specified in
96  * /doc/hsqlSyntax.html#Datatypes. <p>
97  *
98  * To be done:<p>
99  *
100  * <ol>
101  * <li> Implement the "jdbc:default:connection: URL to provide transparent
102  * and portable access to internal connections for use in triggers and
103  * stored procedures. <p>
104  *
105  * <li> Implement declaritive column to trigger method argument
106  * mapping, conditional execution (WHEN clause), etc. <p>
107  *
108  * <li> Investigate and refine synchronous and asynchronous trigger models. <p>
109  *
110  * Because certain combinations of trigger create parameters cause the
111  * individual triggered actions of a multirow update to run in different
112  * threads, it is possible for an 'after' trigger to run before its
113  * corresponding 'before' trigger; the acceptability and implications
114  * of this needs to be investigated, documented and the behaviour of
115  * the engine fully specified.
116  *
117  * <li> Investigate and implement the SQL 200n specified execution stack under
118  * arbitrary triggered action and SQL-invoked routine call graphs.
119  * </ol>
120  *
121  * @author Peter Hudson
122  * @author boucherb@users
123  * @version 1.7.2
124  * @since 1.7.0
125  */
126 public class TriggerSample implements Trigger {
127
128     static final PrintWriter out = new PrintWriter (System.out);
129     static final String drv = "org.hsqldb.jdbcDriver";
130     static final String url = "jdbc:hsqldb:mem:trigger-sample";
131     static final String usr = "sa";
132     static final String pwd = "";
133     static final String impl = TriggerSample.class.getName();
134     static final String tn = "trig_test";
135     static final String drop_test_table_stmt = "DROP TABLE " + tn
136         + " IF EXISTS";
137     static final String create_test_table_stmt = "CREATE TABLE " + tn
138         + "(id INTEGER PRIMARY KEY, value VARCHAR(20))";
139     static final String drop_audit_table_stmt = "DROP TABLE audit IF EXISTS";
140     static final String create_audit_table_stmt = "CREATE TABLE audit("
141         + "id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1), "
142         + "op VARCHAR(6), " + "tn VARCHAR, " + "ors LONGVARCHAR, "
143         + "nrs LONGVARCHAR, " + "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
144     static final String audit_insert_stmt =
145         "INSERT INTO audit(op, tn, ors, nrs) VALUES(?, ?, ?, ?)";
146
147     /**
148      * A sample HSQLDB Trigger interface implementation. <p>
149      *
150      * This sample prints information about the firing trigger and records
151      * actions in an audit table. <p>
152      *
153      * The techniques used here are simplified dramatically for demonstration
154      * purposes and are in no way recommended as a model upon which to build
155      * actual installations involving triggered actions.
156      *
157      * @param typ trigger type
158      * @param trn trigger name
159      * @param tn table name
160      * @param or old row
161      * @param nr new row
162      */
163     public void fire(int typ, String trn, String tn, Object [] or,
164                      Object [] nr) {
165
166         synchronized (TriggerSample.class) {
167             String ors = or == null ? "null"
168                                     : StringUtil.arrayToString(or);
169             String nrs = nr == null ? "null"
170                                     : StringUtil.arrayToString(nr);
171
172             out.println("----------------------------------------");
173             out.println(getTriggerDescriptor(trn, typ, tn));
174             out.println("old row : " + ors);
175             out.println("new row : " + nrs);
176             out.flush();
177
178             if ("TRIG_TEST".equals(tn)) {
179                 switch (typ) {
180
181                     case INSERT_BEFORE_ROW : {
182
183                         // Business rule: ID shall be less than 11.
184 // (Marti DiBergi, we love you ;-)
185 // You can cast row[i] given your knowledge of what
186 // the table format is:
187 final int ID = ((Number ) nr[0]).intValue();
188
189                         doAssert(ID < 11, "ID < 11");
190
191                         break;
192                     }
193                     case UPDATE_BEFORE_ROW : {
194
195                         // Business rule: ignore update of VALUE 'unchangable'.
196 if ("unchangable".equals(or[1])) {
197                             nr[1] = or[1]; // short-circuit the update
198 }
199
200                         // !!!Warning!!!
201 // The engine does not check the class of substituted
202 // values; it's up to you to use the correct class.
203 // For example, this will cause database curruption:
204 // nr[1] = new Integer(5);
205 break;
206                     }
207                 }
208             }
209
210             doAuditStep(typ, tn, ors, nrs);
211         }
212     }
213
214     private static void doAssert(boolean b,
215                                  String msg) throws RuntimeException {
216
217         if (b) {
218
219             // do nothing
220 } else {
221             msg = Trace.getMessage(Trace.ASSERT_FAILED) + ": " + msg;
222
223             throw new RuntimeException (msg);
224         }
225     }
226
227     private static void doAuditStep(int typ, String tn, String ors,
228                                     String nrs) {
229
230         Connection conn;
231         PreparedStatement stmt;
232
233         switch (typ) {
234
235             case INSERT_AFTER_ROW :
236             case UPDATE_AFTER_ROW :
237             case DELETE_AFTER_ROW : {
238                 try {
239                     conn = getConnection();
240                     stmt = conn.prepareStatement(audit_insert_stmt);
241
242                     stmt.setString(1, getOperationSpec(typ));
243                     stmt.setString(2, tn);
244                     stmt.setString(3, ors);
245                     stmt.setString(4, nrs);
246                     stmt.executeUpdate();
247                     conn.close();
248                 } catch (SQLException se) {
249                     se.printStackTrace();
250                 }
251             }
252         }
253     }
254
255     public static String getWhenSpec(int type) {
256
257         switch (type) {
258
259             case INSERT_BEFORE :
260             case INSERT_BEFORE_ROW :
261             case UPDATE_BEFORE :
262             case UPDATE_BEFORE_ROW :
263             case DELETE_BEFORE :
264             case DELETE_BEFORE_ROW : {
265                 return "BEFORE";
266             }
267             case INSERT_AFTER :
268             case INSERT_AFTER_ROW :
269             case UPDATE_AFTER :
270             case UPDATE_AFTER_ROW :
271             case DELETE_AFTER :
272             case DELETE_AFTER_ROW : {
273                 return "AFTER";
274             }
275             default : {
276                 return "";
277             }
278         }
279     }
280
281     public static String getOperationSpec(int type) {
282
283         switch (type) {
284
285             case INSERT_AFTER :
286             case INSERT_AFTER_ROW :
287             case INSERT_BEFORE :
288             case INSERT_BEFORE_ROW : {
289                 return "INSERT";
290             }
291             case UPDATE_AFTER :
292             case UPDATE_AFTER_ROW :
293             case UPDATE_BEFORE :
294             case UPDATE_BEFORE_ROW : {
295                 return "UPDATE";
296             }
297             case DELETE_AFTER :
298             case DELETE_AFTER_ROW :
299             case DELETE_BEFORE :
300             case DELETE_BEFORE_ROW : {
301                 return "DELETE";
302             }
303             default : {
304                 return "";
305             }
306         }
307     }
308
309     public static String getQueueSpec(int qs) {
310         return (qs < 0) ? ""
311                         : ("QUEUE " + qs);
312     }
313
314     public static String getForEachSpec(int type) {
315
316         switch (type) {
317
318             case INSERT_BEFORE_ROW :
319             case INSERT_AFTER_ROW :
320             case UPDATE_BEFORE_ROW :
321             case UPDATE_AFTER_ROW :
322             case DELETE_AFTER_ROW :
323             case DELETE_BEFORE_ROW : {
324                 return "FOR EACH ROW";
325             }
326             default : {
327                 return "";
328             }
329         }
330     }
331
332     public static String getTriggerDDL(String trn, int typ, String tab,
333                                        int qs,
334                                        String impl) throws SQLException {
335
336         StringBuffer sb = new StringBuffer ();
337
338         sb.append("CREATE TRIGGER ");
339         sb.append(trn);
340         sb.append(' ');
341         sb.append(getWhenSpec(typ));
342         sb.append(' ');
343         sb.append(getOperationSpec(typ));
344         sb.append(" ON ");
345         sb.append(tab);
346         sb.append(' ');
347         sb.append(getForEachSpec(typ));
348         sb.append(' ');
349         sb.append(getQueueSpec(qs));
350         sb.append(" CALL \"");
351         sb.append(impl);
352         sb.append("\"");
353
354         return sb.toString();
355     }
356
357     public static String getTriggerDescriptor(String trn, int typ,
358             String tab) {
359
360         StringBuffer sb = new StringBuffer ();
361
362         sb.append("TRIGGER : ");
363         sb.append(trn);
364         sb.append(' ');
365         sb.append(getWhenSpec(typ));
366         sb.append(' ');
367         sb.append(getOperationSpec(typ));
368         sb.append(" ON ");
369         sb.append(tab);
370         sb.append(' ');
371         sb.append(getForEachSpec(typ));
372
373         return sb.toString();
374     }
375
376     private static Connection getConnection() throws SQLException {
377
378         try {
379             Class.forName(drv).newInstance();
380
381             return DriverManager.getConnection(url, usr, pwd);
382         } catch (SQLException se) {
383             throw se;
384         } catch (Exception e) {
385             throw new SQLException (e.toString());
386         }
387     }
388
389     private static void createTrigger(Statement stmt, String trn,
390                                       int typ) throws SQLException {
391         stmt.execute(getTriggerDDL(trn, typ, tn, 0, impl));
392     }
393
394     private static void setup() throws SQLException {
395
396         Connection conn = getConnection();
397         Statement stmt = conn.createStatement();
398
399         stmt.execute(drop_test_table_stmt);
400         stmt.execute(create_test_table_stmt);
401         stmt.execute(drop_audit_table_stmt);
402         stmt.execute(create_audit_table_stmt);
403         createTrigger(stmt, "tib_" + tn, INSERT_BEFORE);
404         createTrigger(stmt, "tibr_" + tn, INSERT_BEFORE_ROW);
405         createTrigger(stmt, "tia_" + tn, INSERT_AFTER);
406         createTrigger(stmt, "tiar_" + tn, INSERT_AFTER_ROW);
407         createTrigger(stmt, "tub_" + tn, UPDATE_BEFORE);
408         createTrigger(stmt, "tubr_" + tn, UPDATE_BEFORE_ROW);
409         createTrigger(stmt, "tua_" + tn, UPDATE_AFTER);
410         createTrigger(stmt, "tuar_" + tn, UPDATE_AFTER_ROW);
411         createTrigger(stmt, "tdb_" + tn, DELETE_BEFORE);
412         createTrigger(stmt, "tdbr_" + tn, DELETE_BEFORE_ROW);
413         createTrigger(stmt, "tda_" + tn, DELETE_AFTER);
414         createTrigger(stmt, "tdar_" + tn, DELETE_AFTER_ROW);
415         stmt.close();
416         conn.close();
417     }
418
419     private static void doSomeWork() throws SQLException {
420
421         Connection conn = getConnection();
422         Statement stmt = conn.createStatement();
423
424         conn.setAutoCommit(false);
425         stmt.execute("INSERT INTO trig_test VALUES (1, 'hello')");
426         stmt.execute("INSERT INTO trig_test VALUES (2, 'now what?')");
427         stmt.execute("INSERT INTO trig_test VALUES (3, 'unchangable')");
428         stmt.execute("INSERT INTO trig_test VALUES (4, 'goodbye')");
429         conn.commit();
430         dumpTable("trig_test");
431         stmt.execute("UPDATE trig_test SET value = 'all done'");
432         conn.commit();
433         dumpTable("trig_test");
434         stmt.execute("DELETE FROM trig_test");
435         conn.rollback();
436         dumpTable("trig_test");
437
438         try {
439             stmt.execute("INSERT INTO trig_test VALUES(11, 'whatever')");
440         } catch (SQLException se) {
441             se.printStackTrace();
442         }
443
444         stmt.execute("INSERT INTO trig_test VALUES(10, 'whatever')");
445         conn.commit();
446         dumpTable("trig_test");
447         stmt.close();
448         conn.close();
449     }
450
451     private static void dumpTable(String tn) throws SQLException {
452
453         Connection conn = getConnection();
454         Statement stmt = conn.createStatement();
455         ResultSet rs = stmt.executeQuery("select * from " + tn);
456         ResultSetMetaData rsmd = rs.getMetaData();
457         int count = rsmd.getColumnCount();
458
459         out.println();
460         out.println("****************************************");
461         out.println("DUMP FOR TABLE: " + tn);
462         out.println("****************************************");
463         out.flush();
464
465         while (rs.next()) {
466             out.print("[");
467
468             for (int i = 1; i <= count; i++) {
469                 out.print(rs.getString(i));
470
471                 if (i < count) {
472                     out.print(" : ");
473                 }
474             }
475
476             out.println("]");
477         }
478
479         out.println();
480         out.flush();
481         rs.close();
482         stmt.close();
483         conn.close();
484     }
485
486     private static void runSample() throws SQLException {
487
488         setup();
489         doSomeWork();
490         dumpTable("audit");
491     }
492
493     public static void main(String [] args) throws SQLException {
494         runSample();
495     }
496 }
497 /*
498     test SQL
499     CREATE CACHED TABLE trig_test (int_field integer)
500     CREATE TRIGGER ins_before BEFORE INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
501     CREATE TRIGGER ins_after AFTER INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
502     CREATE TRIGGER upd_before BEFORE UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
503     CREATE TRIGGER upd_after AFTER UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
504     CREATE TRIGGER upd_before_row BEFORE UPDATE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
505     CREATE TRIGGER upd_after_row AFTER UPDATE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
506     CREATE TRIGGER del_before BEFORE DELETE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
507     CREATE TRIGGER del_after AFTER DELETE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
508     CREATE TRIGGER del_before_row BEFORE DELETE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
509     CREATE TRIGGER del_after_row AFTER DELETE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
510     INSERT INTO trig_test VALUES (1)
511     INSERT INTO trig_test VALUES (2)
512     INSERT INTO trig_test VALUES (3)
513     UPDATE trig_test SET int_field = int_field + 3
514     DELETE FROM trig_test
515  */
516

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags