由於程序的需要，在SQLServer 中創建觸發器及建表，碰到了在java 代碼中執行創建觸發器及表。
/**建立中間表*/
public static final String
createMiddleTableSQL =
"CREATE TABLE [dbo].[AlarmsMiddleTbl] ( "
+ " [id] [int] NOT NULL , "
+ " [DeviceID] [smallint] NULL , "
+ " [Aid] [char] (10) COLLATE Chinese_PRC_BIN NULL , "
+ " [BeginTime] [datetime] NULL , "
+ " [EndTime] [datetime] NULL , "
+ " [Severity] [char] (2) COLLATE Chinese_PRC_BIN NULL , "
+ " [CondType] [char] (25) COLLATE Chinese_PRC_BIN NULL , "
+ " [DetailID] [smallint] NULL , "
+
" [AckNotes] [char] (150) COLLATE Chinese_PRC_BIN NULL , "
+
" [Description] [char] (255) COLLATE Chinese_PRC_BIN NULL , "
+
" [Systemtimes] [char] (30) COLLATE Chinese_PRC_BIN NULL "
+ ") ON [PRIMARY] ";
/**在告警表上創建觸發器*/
public static final String createMiddleTblTriggerSQL =
"CREATE TRIGGER t_alarms "
+ "ON [NTBW].[dbo].[Alarms] "
+ "FOR INSERT, UPDATE "
+ "AS "
+ "DECLARE @rows int "
+ "SELECT @rows = @@rowcount "
+ "IF @rows = 0 "
+ " return "
+
"IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) "
+ "BEGIN "
+ " INSERT INTO [NTBW].[dbo].[AlarmsMiddleTbl] "
+ " SELECT i.[id], i.[DeviceID], i.[Aid], i.[BeginTime], "
+
" i.[EndTime], i.[Severity], i.[CondType], i.[DetailID], i.[AckNotes], "
+ " i.[Description], getdate() from inserted i "
+ "END "
+
"IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) AND "
+ " UPDATE(EndTime) "
+ "BEGIN "
+ " INSERT INTO [NTBW].[dbo].[AlarmsMiddleTbl] "
+ " SELECT i.[id], i.[DeviceID], i.[Aid], i.[BeginTime], "
+
" i.[EndTime], i.[Severity], i.[CondType], i.[DetailID], i.[AckNotes], "
+ " i.[Description], getdate() from inserted i "
+ "END "
+ "IF @@error <> 0 "
+ "BEGIN "
+ " RAISERROR('ERROR',16,1) "
+ " rollback transaction "
+ " return "
+ "END ";
JAVA中執行以上語句過程：
view plaincopy to clipboardprint？
/**
* 創建中間表或者觸發器
*
* @param sql String
* @return boolean 返回語句執行結果,true 成功，false 失敗
*/
private boolean createTableOrTrigger(String sql)
{
Connection con = null;
PreparedStatement st = null;
boolean result = false;
try
{
con = dbh.getConnection();
st = con.prepareStatement(sql);
st.execute();
result = true;
dbh.closeConnections(null, st, con);
}
catch (SQLException ex)
{
Log.error("Unable to create :" +
sql + " ,ErrorCode :" + ex.getErrorCode() +
",Exception :" +
ex.getLocalizedMessage());
dbh.closeConnections(null, st, con);
}
return result;
}
