一个 buffer表和一个 log表。当修改 buffer该表中备货数量。修改记录插入到 log表中。
如:buffer表bufferid,bufferqty,bufferctm
log表bufferid,bufferqty,bufferctm buf01 100 上海
buf02 350 北京
例:buf01 ```100改为60
则:本条信息插入到 log表中做个记录。
写一个触发器,修改一条记录时,会把buffer表所有行信息插入到 log表,我只要插入相应一条记录。
不知触发器那里写错。请帮我指点下或有其它好的方法解决. 谢谢!
CREATE TRIGGER trigUpdateBuffer
ON dbo.BufferDetail
FOR update
AS
IF update(bufferqty)
BEGIN
INSERT INTO DeductLog(
bufferid,bufferqty,bufferctm
)select
bufferid,bufferqty,bufferctm from buffer
end修改一条记录,
如:buffer表bufferid,bufferqty,bufferctm
log表bufferid,bufferqty,bufferctm buf01 100 上海
buf02 350 北京
例:buf01 ```100改为60
则:本条信息插入到 log表中做个记录。
写一个触发器,修改一条记录时,会把buffer表所有行信息插入到 log表,我只要插入相应一条记录。
不知触发器那里写错。请帮我指点下或有其它好的方法解决. 谢谢!
CREATE TRIGGER trigUpdateBuffer
ON dbo.BufferDetail
FOR update
AS
IF update(bufferqty)
BEGIN
INSERT INTO DeductLog(
bufferid,bufferqty,bufferctm
)select
bufferid,bufferqty,bufferctm from buffer
end修改一条记录,
ON dbo.BufferDetail
FOR update
AS
IF update(bufferqty)
BEGIN
INSERT INTO DeductLog(
bufferid,bufferqty,bufferctm
)select
bufferid,bufferqty,bufferctm from inserted
end
ON dbo.BufferDetail
FOR update
AS
IF update(bufferqty)
BEGIN
INSERT INTO DeductLog(
bufferid,bufferqty,bufferctm
)select
bufferid,bufferqty,bufferctm from inserted --inserted 為修改的新數據 如果紀錄舊數據,改為deleted
end
ON dbo.BufferDetail
FOR update
AS
IF update(bufferqty)
BEGIN
INSERT INTO DeductLog( bufferid,bufferqty,bufferctm )
select bufferid,bufferqty,bufferctm
from inserted
end
AS
IF update(bufferqty)
BEGIN
INSERT INTO DeductLog(bufferid,bufferqty,bufferctm) select bufferid,bufferqty,bufferctm from inserted
end
取到客户名称。
如
CREATE TRIGGER trigUpdateBuffer
ON dbo.BufferDetail
FOR update
AS
IF update(ExcessBuffer_Qty)
BEGIN
INSERT INTO DeductLog(
DeductLog.DeductItem,
DeductLog.DeductCtm,
DeductLog.DeductSourceNo,
DeductLog.DeductSourceID,
DeductLog.DeductQty,
DeductLog.DeductDate,
DeductLog.DeductPeople,
DeductLog.BufferNo,
DeductLog.BufferQty,
DeductLog.BufferDetail_id,
DeductLog.DeductIsDel
)select c.item_name,
DeductCtm,
DeductSourceNo,
DeductSourceID,
DeductQty,
getdate(),
DeductPeople,
b.Buffer_no,
BufferDetail_QTY,
BufferDetail_id,0
FROM BufferDetail a INNER JOIN Buffer b
on a.Buffer_ID=b.Buffer_ID
INNER JOIN Item c on a.BufferDetail_Item=c.item_id
end运行还是出现插入多行