|
1
5 package org.h2.test.db;
6
7 import java.sql.Connection  ;
8 import java.sql.ResultSet ;
9 import java.sql.SQLException ;
10 import java.sql.Statement ;
11 import java.util.Arrays ;
12 import java.util.HashSet ;
13
14 import org.h2.api.Trigger;
15 import org.h2.test.TestBase;
16
17
20
21 public class TestTriggersConstraints extends TestBase implements Trigger {
22
23 private static boolean mustNotCallTrigger;
24
25 public void test() throws Exception {
26 deleteDb("trigger");
27 testTriggers();
28 testConstraints();
29 }
30
31 private void testConstraints() throws Exception {
32 Connection conn = getConnection("trigger");
33 Statement stat = conn.createStatement();
34 stat.execute("DROP TABLE IF EXISTS TEST");
35 stat.execute("create table test(id int primary key, parent int)");
36 stat.execute("alter table test add constraint test_parent_id foreign key(parent) references test (id) on delete cascade");
37 stat.execute("insert into test select x, x/2 from system_range(0, 100)");
38 stat.execute("delete from test");
39 checkSingleValue(stat, "select count(*) from test", 0);
40 stat.execute("drop table test");
41 conn.close();
42 }
43
44 private void testTriggers() throws Exception {
45 mustNotCallTrigger = false;
46 Connection conn = getConnection("trigger");
47 Statement stat = conn.createStatement();
48 stat.execute("DROP TABLE IF EXISTS TEST");
49 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
50 stat.execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE BEFORE INSERT ON TEST FOR EACH ROW NOWAIT CALL \""+getClass().getName()+"\"");
52 stat.execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE BEFORE INSERT ON TEST FOR EACH ROW NOWAIT CALL \""+getClass().getName()+"\"");
53 stat.execute("CREATE TRIGGER INS_AFTER AFTER INSERT ON TEST FOR EACH ROW NOWAIT CALL \""+getClass().getName()+"\"");
54 stat.execute("CREATE TRIGGER UPD_BEFORE BEFORE UPDATE ON TEST FOR EACH ROW NOWAIT CALL \""+getClass().getName()+"\"");
55 stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
56 ResultSet rs;
57 rs = stat.executeQuery("SCRIPT");
58 checkRows(rs, new String []{
59 "CREATE TRIGGER PUBLIC.INS_BEFORE BEFORE INSERT ON PUBLIC.TEST FOR EACH ROW NOWAIT CALL \""+getClass().getName()+"\"",
60 "CREATE TRIGGER PUBLIC.INS_AFTER AFTER INSERT ON PUBLIC.TEST FOR EACH ROW NOWAIT CALL \""+getClass().getName()+"\"",
61 "CREATE TRIGGER PUBLIC.UPD_BEFORE BEFORE UPDATE ON PUBLIC.TEST FOR EACH ROW NOWAIT CALL \""+getClass().getName()+"\""
62 });
63 while(rs.next()) {
64 String sql = rs.getString(1);
65 if(sql.startsWith("CREATE TRIGGER")) {
66 System.out.println(sql);
67 }
68 }
69
70 rs = stat.executeQuery("SELECT * FROM TEST");
71 rs.next();
72 check(rs.getString(2), "Hello-updated");
73 checkFalse(rs.next());
74 stat.execute("UPDATE TEST SET NAME=NAME||'-upd'");
75 rs = stat.executeQuery("SELECT * FROM TEST");
76 rs.next();
77 check(rs.getString(2), "Hello-updated-upd-updated2");
78 checkFalse(rs.next());
79
80 mustNotCallTrigger = true;
81 stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
82 stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
83 try {
84 stat.execute("DROP TRIGGER INS_BEFORE");
85 error("must not work");
86 } catch(SQLException e) {
87 checkNotGeneralException(e);
88 }
89 stat.execute("DROP TRIGGER INS_AFTER");
90 stat.execute("DROP TRIGGER UPD_BEFORE");
91 stat.execute("UPDATE TEST SET NAME=NAME||'-upd-notrigger'");
92 stat.execute("INSERT INTO TEST VALUES(100, 'Insert-notrigger')");
93 conn.close();
94
95 conn = getConnection("trigger");
96
97 mustNotCallTrigger = false;
98 conn.close();
99 }
100
101 private void checkRows(ResultSet rs, String [] expected) throws Exception {
102 HashSet set = new HashSet (Arrays.asList(expected));
103 while(rs.next()) {
104 set.remove(rs.getString(1));
105 }
106 if(set.size()>0) {
107 error("set should be empty: " + set);
108 }
109 }
110
111 private String triggerName;
112
113 public void fire(Connection conn, Object [] oldRow, Object [] newRow) throws SQLException {
114 if(mustNotCallTrigger) {
115 throw new Error ("must not be called now");
116 }
117 if(conn == null) {
118 throw new Error ("connection is null");
119 }
120 if(triggerName.startsWith("INS_BEFORE")) {
121 newRow[1] = newRow[1] + "-updated";
122 } else if(triggerName.startsWith("INS_AFTER")) {
123 if(!newRow[1].toString().endsWith("-updated")) {
124 throw new Error ("supposed to be updated");
125 }
126 } else if(triggerName.startsWith("UPD_BEFORE")) {
127 newRow[1] = newRow[1] + "-updated2";
128 } else if(triggerName.startsWith("UPD_AFTER")) {
129 if(!newRow[1].toString().endsWith("-updated2")) {
130 throw new Error ("supposed to be updated2");
131 }
132 }
133 }
134
135 public void init(Connection conn, String schemaName, String triggerName, String tableName) throws SQLException {
136 this.triggerName = triggerName;
137 if(!"TEST".equals(tableName)) {
138 throw new Error ("supposed to be TEST");
139 }
140 }
141
142 }
143 |
Popular Tags |
Java API By Example, From Geeks To Geeks.