表A 字段 ID kuncun xiaoliang
1 50 0
2 100 0
表B字段 ID xiaoshou
1 20
2 10
表B为销售表 ,表A为商品表,当表B加入销售的商品后,表A作相应的修改,sql语句该怎么写 既表A变为 ID kuncun xiaoliang
1 30 20
2 90 10
1 50 0
2 100 0
表B字段 ID xiaoshou
1 20
2 10
表B为销售表 ,表A为商品表,当表B加入销售的商品后,表A作相应的修改,sql语句该怎么写 既表A变为 ID kuncun xiaoliang
1 30 20
2 90 10
RetrieveObject retrieve = new RetrieveObject(); //RetrieveObject类用来检索数据库
java.util.Collection collection = null;
java.util.Iterator iterator = null;
collection = retrieve.getTableCollection(sqlStr); //getTableCollection方法用来把数据库里满足sql语句的数据放到一个集合对象里
iterator = collection.iterator();
String[] gID = new String[collection.size()];
String[] gNumber = new String[collection.size()];
int i = 0;
while(iterator.hasNext()){
java.util.Vector vdata = (java.util.Vector)iterator.next();
gID[i] = String.valueOf(vdata.get(0));
gNumber[i] = String.valueOf(vdata.get(1));
i++;
}
int j;
for(j=0;j<i;j++){
String sql="update tb_Shangpin set goodsKucun=goodsKucun-"+gNumber[j]+" ,goodsSell=goodsSell+"+gNumber[j]+" where goodsID='"+gID[j]+"'"; //tb_Shangpin时商品表,goodsKucun,goodsSell 分别为商品库存和销量
JdbcAdapter jd=new JdbcAdapter(); //JdbcAdapter类的UPdate方法用来执行sql语句
jd.UPdate(sql);
}
用这样的语句能实现我觉得太复杂了 ,有没有什么好的方法
1:代码里面算,update
2:触发器
AFTER UPDATE
ON B
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
update A set kucun = kucun - NEW.xiaoshou + OLD.xiaoshou,xiaoliang = xiaoliang + NEW.xiaoshou - OLD.xiaoshou where id = new.id
end