|
1
5 package org.h2.samples;
6
7 import java.math.BigDecimal  ;
8 import java.sql.Connection ;
9 import java.sql.DriverManager ;
10 import java.sql.PreparedStatement ;
11 import java.sql.ResultSet ;
12 import java.sql.SQLException ;
13 import java.sql.Statement ;
14
15 import org.h2.api.Trigger;
16
17 public class TriggerSample {
18
19 public static void main(String [] args) throws Exception {
20 Class.forName("org.h2.Driver");
21 Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
22 Statement stat = conn.createStatement();
23 stat.execute("CREATE TABLE INVOICE(ID INT PRIMARY KEY, AMOUNT DECIMAL)");
24 stat.execute("CREATE TABLE INVOICE_SUM(AMOUNT DECIMAL)");
25 stat.execute("INSERT INTO INVOICE_SUM VALUES(0.0)");
26 stat.execute("CREATE TRIGGER INV_INS AFTER INSERT ON INVOICE FOR EACH ROW CALL \"org.h2.samples.TriggerSample$MyTrigger\" ");
27 stat.execute("CREATE TRIGGER INV_UPD AFTER UPDATE ON INVOICE FOR EACH ROW CALL \"org.h2.samples.TriggerSample$MyTrigger\" ");
28 stat.execute("CREATE TRIGGER INV_DEL AFTER DELETE ON INVOICE FOR EACH ROW CALL \"org.h2.samples.TriggerSample$MyTrigger\" ");
29
30 stat.execute("INSERT INTO INVOICE VALUES(1, 10.0)");
31 stat.execute("INSERT INTO INVOICE VALUES(2, 19.95)");
32 stat.execute("UPDATE INVOICE SET AMOUNT=20.0 WHERE ID=2");
33 stat.execute("DELETE FROM INVOICE WHERE ID=1");
34
35 ResultSet rs;
36 rs = stat.executeQuery("SELECT AMOUNT FROM INVOICE_SUM");
37 rs.next();
38 System.out.println("The sum is " + rs.getBigDecimal(1));
39 conn.close();
40 }
41
42 public static class MyTrigger implements Trigger {
43
44 public void init(Connection conn, String schemaName, String triggerName, String tableName) {
45 }
47
48 public void fire(Connection conn,
49 Object [] oldRow, Object [] newRow)
50 throws SQLException {
51 BigDecimal diff = null;
52 if(newRow != null) {
53 diff = (BigDecimal )newRow[1];
54 }
55 if(oldRow != null) {
56 BigDecimal m = (BigDecimal )oldRow[1];
57 diff = diff == null ? m.negate() : diff.subtract(m);
58 }
59 PreparedStatement prep = conn.prepareStatement(
60 "UPDATE INVOICE_SUM SET AMOUNT=AMOUNT+?");
61 prep.setBigDecimal(1, diff);
62 prep.execute();
63 }
64 }
65
66 }
67 |
Popular Tags |
Java API By Example, From Geeks To Geeks.