本文是小小的总结一下本人开发时要在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似乎不支持中文创表，和记录中有中午，也许是我还不够了解，懂的朋友欢迎指导
最后，这是第一次写技术博客，就大概贴出了代码，还有很多不足的地方，请大家多多指教，欢迎一起探讨共同进步。
