一個入庫的主副表,新增,保存,刪除數据都出錯,數据庫是ms sql"找不到要更新的资料列,最后读取的值已经变更"由於數据庫有触發器,刪除后一切正常如何解決這個問題
解决方案 »
- 初来乍到,一点小疑问,顺便散分!
- 送分,用WebSnap开发的网页如何调试!很急,在线等!
- 对我来说是大问题!!!
- 大虾::帮忙!
- 我在DATABASE DESKTOP里新建的表如何才能保存?
- 怎样才能把存储进数据库中的AutoCAD文件还原成.dwg文件?
- 怎么打开扩展名为.mb的数据库?要用什么软件才可以编辑它?
- 用delphi做组件是如何和asp进行交互的呢?(能给段简单的代码看看么?)
- Combobox方面的
- 下面两句程序的意思,麻烦高手帮我解释!
- 控件Ehlib3安装时提示: File not found: 'DBSumLst.dcu',请问这是为什么??
- 请问:DBGRIDEH控件哪里有下载?
可以通过timestamp类型的附加字段解决。或者用锁。
FOR INSERT
AS
/*更新amstock*/
UPDATE A SET
qty = A.qty +I.qty
FROM amstock AS A INNER JOIN (select materialID,materialID1,warehouseID,sum(qty) as qty from Inserted group by materialID,materialID1,warehouseID) AS I
ON A.materialID = I.materialID and A.materialID1 = I.materialID1 and A.warehouseID=I.warehouseID insert into amstock (materialID,materialID1,qty,warehouseID)
select materialID,materialID1,sum(qty) as qty,warehouseID from Inserted I
where not exists (
select 1 FROM amstock AS A where A.materialID = I.materialID and A.materialID1 = I.materialID1 and A.warehouseID=I.warehouseID
)
group by materialID,materialID1,warehouseID
/*更新amdate*/
UPDATE M SET
mdate =I.mdate
FROM amdate AS M INNER JOIN (SELECT Inserted.materialID,Inserted.materialID1,max(purproductmaster.purchasedate) AS mdate
FROM purproductmaster INNER JOIN Inserted ON purproductmaster.purchaseID = Inserted .purchaseID GROUP BY Inserted.materialID,Inserted.materialID1) AS I
ON M.materialID = I.materialID and M.materialID1 = I.materialID1
Where M.mdate<I.mdate insert into amdate (materialID,materialID1,mdate)
SELECT Inserted.materialID,Inserted.materialID1,max(purproductmaster.purchasedate) AS mdate
FROM purproductmaster INNER JOIN Inserted ON purproductmaster.purchaseID = Inserted .purchaseID
where not exists ( select 1 FROM amdate AS M where M.materialID = Inserted.materialID and M.materialID = Inserted.materialID )
GROUP BY Inserted.materialID,Inserted.materialID1
{ adodetail->UpdateBatch();
adodetail->DataSource=dstable;
if(dm->ADOC->InTransaction)
{
dm->ADOC->CommitTrans();
}
}
你是附表有insert触发器,你在附表insert的时候要做些什么? 解释一下
3 purchaseID nvarchar 20 0
0 purchasedate datetime 8 0
0 supplierID nvarchar 20 0
0 employeeID nvarchar 4 1
0 rem nvarchar 40 1從表:purproductdetail1 purchaseID nvarchar 20 0
0 pno nvarchar 20 1
0 invID nvarchar 10 1
0 warehouseID nvarchar 20 1
0 materialID nvarchar 20 0
0 materialID1 nvarchar 10 1
0 purchasedetailID int 4 1
0 qty decimal 9 1
0 price decimal 9 1
0 price1 decimal 9 1
0 money decimal 9 1
0 money1 decimal 9 1
0 rem nvarchar 20 1
庫存表: amstock
1 materialID nvarchar 20 0
0 materialID1 nvarchar 10 0
0 warehouseID nvarchar 20 0
0 QTY decimal 9 1
0 price decimal 9 1
0 price1 decimal 9 1
0 money decimal 9 1
0 money1 decimal 9 1每次新時都出錯:找不到要更新的资料列,最后读取的值已经变更
就像我们写这样的句子,在界面里面不能修改数据一样的到了
FOR INSERT
AS
SET NOCOUNT ON
...
能不能說詳細點,謝謝!!!!!!!1
set nocount on
set nocount off
像这样:REATE TRIGGER purproductdetail_insert ON dbo.purproductdetail
FOR INSERT
AS
SET NOCOUNT ON
...这中间是你在tigger中要做的事
SET NOCOUNT OFF
把SQL Server的这个功能关闭就解决了。