-
Notifications
You must be signed in to change notification settings - Fork 77
Expand file tree
/
Copy pathtestMssqlTrigger.java
More file actions
78 lines (69 loc) · 3.67 KB
/
Copy pathtestMssqlTrigger.java
File metadata and controls
78 lines (69 loc) · 3.67 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
package common;
/*
* Date: 2010-9-1
* Time: 16:36:33
*/
import gudusoft.gsqlparser.ESqlStatementType;
import gudusoft.gsqlparser.ETriggerActionTime;
import gudusoft.gsqlparser.nodes.TDmlEventClause;
import gudusoft.gsqlparser.nodes.TDmlEventItem;
import gudusoft.gsqlparser.nodes.TSimpleDmlTriggerClause;
import gudusoft.gsqlparser.stmt.TCreateTriggerStmt;
import junit.framework.TestCase;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.nodes.TDeclareVariable;
import gudusoft.gsqlparser.stmt.mssql.*;
public class testMssqlTrigger extends TestCase {
private TGSqlParser parser = null;
protected void setUp() throws Exception {
super.setUp();
parser = new TGSqlParser(EDbVendor.dbvmssql);
}
protected void tearDown() throws Exception {
parser = null;
super.tearDown();
}
public void test1(){
parser.sqltext = "CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader\n" +
"AFTER INSERT\n" +
"AS\n" +
"DECLARE @creditrating tinyint, @vendorid int;\n" +
"IF EXISTS (SELECT *\n" +
" FROM Purchasing.PurchaseOrderHeader p \n" +
" JOIN inserted AS i \n" +
" ON p.PurchaseOrderID = i.PurchaseOrderID \n" +
" JOIN Purchasing.Vendor AS v \n" +
" ON v.BusinessEntityID = p.VendorID\n" +
" WHERE v.CreditRating = 5\n" +
" )\n" +
"BEGIN\n" +
"RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1);\n" +
"ROLLBACK TRANSACTION;\n" +
"RETURN \n" +
"END;" ;
assertTrue(parser.parse() == 0);
TCreateTriggerStmt stmt = (TCreateTriggerStmt)parser.sqlstatements.get(0);
assertEquals(true, stmt.getTriggerName().toString().equalsIgnoreCase("Purchasing.LowCredit"));
assertTrue(stmt.getOnTable().toString().equalsIgnoreCase("Purchasing.PurchaseOrderHeader"));
// dml trigger
assertTrue(stmt.getTriggeringClause() instanceof TSimpleDmlTriggerClause);
TSimpleDmlTriggerClause dmlTriggerClause = (TSimpleDmlTriggerClause)stmt.getTriggeringClause();
assertTrue(dmlTriggerClause.getActionTime() == ETriggerActionTime.tatAfter);
TDmlEventClause dmlEventClause = (TDmlEventClause)dmlTriggerClause.getEventClause();
assertTrue(dmlEventClause.getTableName().toString().equalsIgnoreCase("Purchasing.PurchaseOrderHeader"));
TDmlEventItem eventItem = (TDmlEventItem)dmlEventClause.getEventItems().get(0);
assertTrue(eventItem.getDmlType() == ESqlStatementType.sstinsert);
assertTrue(stmt.getBodyStatements().size() == 2);
TMssqlDeclare declare = (TMssqlDeclare)stmt.getBodyStatements().get(0);
assertTrue(declare.getVariables().size() == 2);
TDeclareVariable var1 = (TDeclareVariable)declare.getVariables().getDeclareVariable(0);
assertTrue(var1.getVariableName().toString().equalsIgnoreCase("@creditrating"));
assertTrue(var1.getDatatype().toString().equalsIgnoreCase("tinyint"));
TMssqlIfElse ifstmt = (TMssqlIfElse)stmt.getBodyStatements().get(1);
TMssqlBlock block = (TMssqlBlock)ifstmt.getStmt();
TMssqlRaiserror raiserror = (TMssqlRaiserror)block.getStatements().get(0);
assertTrue(raiserror.getMessageText().toString().equalsIgnoreCase("'This vendor''s credit rating is too low to accept new purchase orders.'"));
//System.out.println(block.getStatements().get(2).sqlstatementtype);
}
}