本文是小小的總結一下本人開發時要在sybase數據庫上創建特定的觸發器
創建的觸發器完成的功能如下 ：
在數據庫中指定的表上創建插入，更新，刪除觸發器，當指定的表發生插入、更新或者刪除操作時，將觸發觸發器相應的動作，觸發器的作用就是，將發生上述操作的表的表名、主鍵名、對應的主鍵值、相應的操作存到另一張記錄表中。本總結中除了創建該類型觸發器外還包括刪除觸發器，刪除記錄表，判斷是否已有記錄表。
需要導入的架包：activejdbc.jar,jconn3.jar:
import
java.util.List;
import
java.util.Map;
import
org.javalite.activejdbc.Base;
import
org.slf4j.Logger;
import
org.slf4j.LoggerFactory;
/**
* 模板類
*
@author
ywnwa
*
*/
public
abstract
class
AbstractTriggerManager
implements
TriggerManager {
protected
static
final
String TRIGGER_EVENT_TABLE_NAME = "TRIGGER_EVENTS"
;
private
final
Logger logger =
LoggerFactory
.getLogger(getClass());
private
final
String user;
private
final
String password;
public
AbstractTriggerManager(String user, String password) {
this
.user =
user;
this
.password =
password;
}
public
void
openDatabase() {
Base.open(getDriverClass(), getUrl(),
this
.user,
this
.password);
logger.debug(
"open database sucessfuly!"
);
}
public
void
closeDatabase() {
Base.close();
logger.debug(
"close database sucessfuly!"
);
}
@SuppressWarnings(
"rawtypes"
)
public
boolean
hasEventTable() {
//
NOTE (Tan Bingjian)
//
the values of table_name must be given in capital
List<Map> result =
Base.findAll(getSelectTriggerEventTableSql());
return
!
result.isEmpty();
}
public
void
createEventTable() {
if
(!
hasEventTable()) {
logger.debug(
"TRIGGER_EVENTS is not exist! It will be created ."
);
Base.exec(getCreateEventTableSql());
logger.debug(
"TRIGGER_EVENTS create successfuly!"
);
}
logger.debug(
"TRIGGER_EVENTS is get ready !"
);
}
public
void
dropEventTable() {
if
(hasEventTable()) {
Base.exec(getDropEventTableSql());
logger.debug(
"TRIGGER_EVENTS drop successfuly!"
);
}
}
@Override
public
void
create(String name) {
//
trigger for insert
Base.exec(sqlForTrigger(name, "insert"
));
//
trigger for delete
Base.exec(sqlForTrigger(name, "delete"
));
//
trigger for update
Base.exec(sqlForTrigger(name, "update"
));
logger.debug(
"The triggers on table " +
name
+ " create successfuly ! !"
);
}
@Override
public
void
remove(String tableName) {
String[] actions
= getActionsType();
//
{ "INSERT", "UPDATE", "DELETE" };
for
(String i : actions) {
String triggerName
= String.format("TR_%s_%s"
, tableName, i);
logger.debug(
"look for " + triggerName + " on table :" +
tableName);
if
(triggerExists(tableName, triggerName)) {
dropTrigger(triggerName);
logger.debug(
"the trigger has been deleted !"
);
}
else
{
logger.debug(
"the trigger no existe !"
);
}
}
}
@Override
public
void
removeAll() {
List
<String> tableNames =
getAllTables();
for
(String name : tableNames) {
remove(name);
}
}
//
@Override
public
void
removeAll(
boolean
removeEventTable) {
removeAll();
if
(removeEventTable &&
hasEventTable()) {
dropEventTable();
}
}
@SuppressWarnings(
"rawtypes"
)
public
boolean
triggerExists(String tableName, String triggerName) {
List
<Map> result =
Base.findAll(getSelectTriggersSql(tableName,
triggerName));
return
!
result.isEmpty();
}
protected
void
dropTrigger(String triggerName) {
Base.exec(getDropTriggerSql(triggerName));
}
protected
String getEventTableName() {
return
TRIGGER_EVENT_TABLE_NAME;
}
protected
abstract
List<String>
getAllTables();
protected
abstract
String getDriverClass();
protected
abstract
String getUrl();
protected
abstract
String getSelectTriggerEventTableSql();
protected
abstract
String sqlForTrigger(String tableName, String opType);
protected
abstract
String getSelectTriggersSql(String tableName,
String triggerName);
protected
abstract
String getDropTriggerSql(String triggerName);
protected
abstract
String getCreateEventTableSql();
protected
abstract
String getDropEventTableSql();
protected
abstract
String[] getActionsType();
}
這個只是模板類不做過多介紹，下面是具體的實現類：
import
java.util.ArrayList;
import
java.util.Iterator;
import
java.util.List;
import
java.util.Map;
import
java.util.Set;
import
org.javalite.activejdbc.Base;
import
org.slf4j.Logger;
import
org.slf4j.LoggerFactory;
public
class
SyBaseTriggerManager
extends
AbstractTriggerManager {
private
final
Logger logger =
LoggerFactory
.getLogger(getClass());
private
static
final
String URL_TEMPLATE = "jdbc:sybase:Tds:%s:%s/%s"
;
private
static
final
String SELECT_TABLES_SQL_TEMPLATE = "select name from sysobjects where type='U'"
;
private
static
final
String DRIVER_CLASS = "com.sybase.jdbc3.jdbc.SybDriver"
;
private
static
final
String TRIGGER_EVENT_TABLE_NAME = "TRIGGER_EVENTS"
;
private
static
final
String SELECT_TRIGGER_EVENT_TABLE_SQL = "select name from sysobjects where name='"
+
TRIGGER_EVENT_TABLE_NAME
+ "'"
;
private
static
final
String CREATE_TRIGGER_EVENT_TABLE_SQL = "CREATE TABLE "
+
TRIGGER_EVENT_TABLE_NAME
+ "(TRIGGER_EVENTS_ID INT IDENTITY NOT NULL PRIMARY KEY ,"
+ " dbName varchar(30),"
+ " tableName varchar(30),"
+ " pkNames varchar(2000),"
+ " pkValues varchar(2000),"
+ " action varchar(20))"
;
private
static
final
String SELECT_PK_NAMES_SQL_TEMPLATE = "select columnname from (SELECT object_name(id) tabname, index_col( object_name(id) ,indid,1) columnname FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,2) FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,3) FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,4) FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,5) FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,6) FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,7) FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,8) FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,9) FROM sysindexes WHERE status & 2048=2048 "
+ "union "
+ "SELECT object_name(id), index_col( object_name(id) ,indid,10) FROM sysindexes WHERE status & 2048=2048 "
+ ")pk where columnname is not null and tabname='%s'"
;
private
static
final
String SELECT_TRIGGERS_SQL_TEMPLATE = "select name from sysobjects where type = 'TR'and name='%s'"
;
private
static
final
String DROP_TRIGGER_SQL_TEMPLATE = "drop trigger %s"
;
private
static
final
String DROP_TRIGGER_EVENT_TABLE_SQL = "drop table "
+
TRIGGER_EVENT_TABLE_NAME;
private
final
String url;
private
final
String host;
private
final
int
port;
private
final
String db;
private
final
String user;
private
final
String password;
public
SyBaseTriggerManager(String host,
int
port, String db, String user,
String password) {
super
(user, password);
this
.db =
db;
this
.user =
user;
this
.port =
port;
this
.host =
host;
this
.url =
String.format(URL_TEMPLATE, host, port, db);
this
.password =
password;
}
@SuppressWarnings(
"rawtypes"
)
@Override
protected
List<String>
getAllTables() {
List
<Map> result =
Base.findAll(SELECT_TABLES_SQL_TEMPLATE);
List
<String> names =
new
ArrayList<String>
();
for
(Map row : result) {
names.add(row.get(
"name"
).toString());
}
return
names;
}
protected
String getHost() {
return
host;
}
protected
int
getPort() {
return
port;
}
protected
String getDb() {
return
db;
}
protected
String getUser() {
return
user;
}
protected
String getPassword() {
return
password;
}
@Override
protected
String getDriverClass() {
return
DRIVER_CLASS;
}
@Override
protected
String getUrl() {
return
url;
}
@Override
protected
String getSelectTriggerEventTableSql() {
return
SELECT_TRIGGER_EVENT_TABLE_SQL;
}
@Override
protected
String sqlForTrigger(String tableName, String opType) {
String actionTo;
String[] pkeys
=
getPks(tableName);
String tigger;
String va
= ""
;
String declare
= ""
;
String values
= ""
;
String pk
= ""
;
if
(opType.equals("insert") || opType.equals("update"
)) {
actionTo
= "inserted "
;
}
else
{
actionTo
= "deleted "
;
}
for
(
int
i = 0; i < pkeys.length; i++
) {
pk
+= pkeys[i] + ","
;
va
+= "@" + pkeys[i] + "+" + "','" + "+"
;
declare
+= "@" + pkeys[i] + " varchar(20)" + ","
;
values
+= "select " + "@" + pkeys[i] + "=convert(char(200),"
+ pkeys[i] + ")" + " from " +
actionTo;
}
declare
= declare.substring(0, declare.lastIndexOf(","
));
va
= va.substring(0, va.lastIndexOf("+"
));
tigger
= "create trigger TR_%s_%s on %s"
+ " for %s as declare %s begin" + " %s insert into %s"
+ "(dbName,tableName,pkNames,pkValues,action)"
+ "values('%s','%s','%s',%s,'%s') end"
;
return
String.format(tigger, tableName, opType, tableName, opType,
declare, values, TRIGGER_EVENT_TABLE_NAME, db, tableName, pk,
va, opType);
}
@Override
protected
String getSelectTriggersSql(String tableName, String triggerName) {
return
String.format(SELECT_TRIGGERS_SQL_TEMPLATE, triggerName);
}
@Override
protected
String getDropTriggerSql(String triggerName) {
return
String.format(DROP_TRIGGER_SQL_TEMPLATE, triggerName);
}
@Override
protected
String getCreateEventTableSql() {
return
CREATE_TRIGGER_EVENT_TABLE_SQL;
}
@Override
protected
String getDropEventTableSql() {
return
DROP_TRIGGER_EVENT_TABLE_SQL;
}
@SuppressWarnings({
"rawtypes"
})
public
String[] getPks(String tableName) {
ArrayList
<Map> list3 =
new
ArrayList<Map>
();
list3
= (ArrayList<Map>
) Base.findAll(String.format(
SELECT_PK_NAMES_SQL_TEMPLATE, tableName));
String[] pkNames
=
new
String[list3.size()];
for
(
int
i = 0; i < list3.size(); i++
) {
Map map
=
list3.get(i);
Set set
=
map.keySet();
Iterator it
=
set.iterator();
while
(it.hasNext()) {
pkNames[i]
=
(String) map.get(it.next());
System.out.println(pkNames[i]);
}
}
return
pkNames;
}
public
void
create(String tableName) {
String[] actions
= { "insert", "update", "delete"
};
for
(String i : actions) {
String triggerName
= String.format("TR_%s_%s"
, tableName, i);
if
(triggerExists(tableName, triggerName)) {
dropTrigger(triggerName);
}
Base.exec(sqlForTrigger(tableName, i));
}
logger.debug(
"The triggers on table " +
tableName
+ " create successfuly ! !"
);
}
@Override
protected
String[] getActionsType() {
String[] actions
= { "insert", "update", "delete"
};
return
actions;
}
}
關鍵語句介紹：
private static final String SELECT_PK_NAMES_SQL_TEMPLATE
獲取對應表的主鍵名語句，即對應表的主鍵名，不過這條語句有個局限性是最多只能獲取表中只有10個主鍵的表，超過十個的話第十一個主鍵將不再獲取。如果大家有更加自由的方法歡迎交流。
?protected String sqlForTrigger(String tableName, String opType)
該方法返回創建觸發器語句
創建觸發器語句：return返回的是完整的語句
tigger = "create trigger TR_%s_%s on %s"
+ " for %s as declare %s begin" + " %s insert into %s"
+ "(dbName,tableName,pkNames,pkValues,action)"
+ "values('%s','%s','%s',%s,'%s') end"
;
return
String.format(tigger, tableName, opType, tableName, opType,
declare, values, TRIGGER_EVENT_TABLE_NAME, db, tableName, pk,
va, opType);
對了，sybase數據庫對大小寫敏感，還有就是sybase似乎不支持中文創表，和記錄中有中午，也許是我還不夠了解，懂的朋友歡迎指導
最后，這是第一次寫技術博客，就大概貼出了代碼，還有很多不足的地方，請大家多多指教，歡迎一起探討共同進步。
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
