01: /*
02: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
03: * (license2)
04: * Initial Developer: H2 Group
05: */
06: package org.h2.samples;
07:
08: import java.math.BigDecimal;
09: import java.sql.Connection;
10: import java.sql.DriverManager;
11: import java.sql.PreparedStatement;
12: import java.sql.ResultSet;
13: import java.sql.SQLException;
14: import java.sql.Statement;
15:
16: import org.h2.api.Trigger;
17:
18: /**
19: * This sample application shows how to use database triggers.
20: */
21: public class TriggerSample {
22:
23: public static void main(String[] args) throws Exception {
24: Class.forName("org.h2.Driver");
25: Connection conn = DriverManager.getConnection("jdbc:h2:mem:",
26: "sa", "");
27: Statement stat = conn.createStatement();
28: stat
29: .execute("CREATE TABLE INVOICE(ID INT PRIMARY KEY, AMOUNT DECIMAL)");
30: stat.execute("CREATE TABLE INVOICE_SUM(AMOUNT DECIMAL)");
31: stat.execute("INSERT INTO INVOICE_SUM VALUES(0.0)");
32: stat
33: .execute("CREATE TRIGGER INV_INS AFTER INSERT ON INVOICE FOR EACH ROW CALL \"org.h2.samples.TriggerSample$MyTrigger\" ");
34: stat
35: .execute("CREATE TRIGGER INV_UPD AFTER UPDATE ON INVOICE FOR EACH ROW CALL \"org.h2.samples.TriggerSample$MyTrigger\" ");
36: stat
37: .execute("CREATE TRIGGER INV_DEL AFTER DELETE ON INVOICE FOR EACH ROW CALL \"org.h2.samples.TriggerSample$MyTrigger\" ");
38:
39: stat.execute("INSERT INTO INVOICE VALUES(1, 10.0)");
40: stat.execute("INSERT INTO INVOICE VALUES(2, 19.95)");
41: stat.execute("UPDATE INVOICE SET AMOUNT=20.0 WHERE ID=2");
42: stat.execute("DELETE FROM INVOICE WHERE ID=1");
43:
44: ResultSet rs;
45: rs = stat.executeQuery("SELECT AMOUNT FROM INVOICE_SUM");
46: rs.next();
47: System.out.println("The sum is " + rs.getBigDecimal(1));
48: conn.close();
49: }
50:
51: /**
52: * This class is a simple trigger implementation.
53: */
54: public static class MyTrigger implements Trigger {
55:
56: /**
57: * Initializes the trigger.
58: *
59: * @param conn a connection to the database
60: * @param schemaName the name of the schema
61: * @param triggerName the name of the trigger used in the CREATE TRIGGER
62: * statement
63: * @param tableName the name of the table
64: * @param before whether the fire method is called before or after the
65: * operation is performed
66: * @param type the operation type: INSERT, UPDATE, or DELETE
67: */
68: public void init(Connection conn, String schemaName,
69: String triggerName, String tableName, boolean before,
70: int type) {
71: // Initializing trigger
72: }
73:
74: /**
75: * This method is called for each triggered action.
76: *
77: * @param conn a connection to the database
78: * @param oldRow the old row, or null if no old row is available (for INSERT)
79: * @param newRow the new row, or null if no new row is available (for DELETE)
80: * @throws SQLException if the operation must be undone
81: */
82: public void fire(Connection conn, Object[] oldRow,
83: Object[] newRow) throws SQLException {
84: BigDecimal diff = null;
85: if (newRow != null) {
86: diff = (BigDecimal) newRow[1];
87: }
88: if (oldRow != null) {
89: BigDecimal m = (BigDecimal) oldRow[1];
90: diff = diff == null ? m.negate() : diff.subtract(m);
91: }
92: PreparedStatement prep = conn
93: .prepareStatement("UPDATE INVOICE_SUM SET AMOUNT=AMOUNT+?");
94: prep.setBigDecimal(1, diff);
95: prep.execute();
96: }
97: }
98:
99: }
|