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