Create Trigger RefreshStock
on InStock
FOR INSERT, UPDATE, DELETE
As
if exists (select * from inserted ) and not exists (select * from deleted)
begin
update parts set qty=(select inqty from inserted ) where partno in (select partno from inserted)
end
if not exists (select * from inserted) and exists (select * from deleted )
begin
delete from parts where partno in (select partno from deleted)
end
if exists (select * from inserted) and exists (select * from deleted)
begin
update parts set qty=(select inqty from inserted) where partno in (select partno from inserted)
end
on InStock
FOR INSERT, UPDATE, DELETE
As
if exists (select * from inserted ) and not exists (select * from deleted)
begin
update parts set qty=(select inqty from inserted ) where partno in (select partno from inserted)
end
if not exists (select * from inserted) and exists (select * from deleted )
begin
delete from parts where partno in (select partno from deleted)
end
if exists (select * from inserted) and exists (select * from deleted)
begin
update parts set qty=(select inqty from inserted) where partno in (select partno from inserted)
end
on InStock
FOR INSERT, UPDATE, DELETE
As
--insert
if exists (select * from inserted ) and not exists (select * from deleted)
begin
--如果parts不存在记录,那么就要插入;否则要update
update parts set qty=qty+tt.inqty
from inserted as tt
where parts.partno=tt.partnoinsert parts
select partno, inqty from inserted as tt
where not exists(select 1 from parts
where tt.partno=parts.partno)
end
--delete
if not exists (select * from inserted) and exists (select * from deleted )
begin
update parts set qty=qty-tt.inqty
from deleted as tt
where parts.partno=tt.partno
end
--update
if exists (select * from inserted) and exists (select * from deleted)
begin
update parts set qty=qty-tt.inqty
from deleted as tt
where parts.partno=tt.partnoupdate parts set qty=qty+tt.inqty
from inserted as tt
where parts.partno=tt.partno
end
go
--测试一
begin tran
select * from instock
select * from parts
delete instock
select * from instock
select * from parts
rollback tran
--测试二
begin tran
select * from instock
select * from parts
update instock set inqty=200 where no>2
select * from instock
select * from parts
rollback tran
--测试三
begin tran
select * from instock
select * from parts
insert instock select 7, 'seven', 10
select * from instock
select * from parts
rollback tran
--清除
drop Trigger RefreshStock
CREATE TRIGGER trg_instock_delete ON [dbo].[InStock]
FOR DELETE
AS
declare @PartNo as varchar(10)
declare @InQty as int
DECLARE cur_instock CURSOR FOR select PartNo,InQty from deleted
open cur_instock
FETCH NEXT FROM cur_instock INTO @PartNo, @InQtyWHILE @@FETCH_STATUS = 0
BEGIN
print @PartNo
print @InQty
update Parts set Qty = Qty - @InQty where PartNo = @PartNo
FETCH NEXT FROM cur_instock INTO @PartNo, @InQty
end
CLOSE cur_instock
DEALLOCATE cur_instock以上测试通过,你在按照以上方法写个新增数据的触发器(新增数据从inserted表取得),更新数据时,系统自动处理为删除并新增数据
CREATE TRIGGER trg_instock_delete ON [dbo].[InStock]
FOR DELETE ,update
AS
declare @PartNo as varchar(10)
declare @InQty as int
DECLARE cur_instock CURSOR FOR select PartNo,InQty from deleted
open cur_instock
FETCH NEXT FROM cur_instock INTO @PartNo, @InQtyWHILE @@FETCH_STATUS = 0
BEGIN
print @PartNo
print @InQty
update Parts set Qty = Qty - @InQty where PartNo = @PartNo
FETCH NEXT FROM cur_instock INTO @PartNo, @InQty
end
CLOSE cur_instock
DEALLOCATE cur_instock新增、修改时
CREATE TRIGGER trg_instock_insert ON [dbo].[InStock]
FOR INSERT ,update
AS
declare @PartNo as varchar(10)
declare @InQty as int
DECLARE cur_instock CURSOR FOR select PartNo,InQty from inserted
open cur_instock
FETCH NEXT FROM cur_instock INTO @PartNo, @InQtyWHILE @@FETCH_STATUS = 0
BEGIN
print @PartNo
print @InQty
update Parts set Qty = Qty + @InQty where PartNo = @PartNo
FETCH NEXT FROM cur_instock INTO @PartNo, @InQty
end
CLOSE cur_instock
DEALLOCATE cur_instock
接受 to filebat(Mark) : 的提示和建议就此更正一下:Create Trigger RefreshStock
on InStock
FOR INSERT, UPDATE, DELETE
As
--当测试动作是insert的话:
/*
如果再考虑细点的话,可以这样的,如果向入库表中插入一条记录的话,
如果库存表中存在的话,则库存qty字段的值就汇总,如果不存在
就插入一条新的记录。
*/
if exists (select * from inserted ) and not exists (select * from deleted)
begin
if exists(select parts.partno from parts join inserted on parts.partno =inserted.partno)
begin
update parts set qty=(select sum(inqty) from instock where partno in (select partno from inserted))
where partno in (select partno from inserted)
end
else
begin
insert parts select partno,inqty from inserted
end
end
--如果测试动作是delete的话。if not exists (select * from inserted) and exists (select * from deleted )
begin
delete from parts where partno in (select partno from deleted)
end--如果测试动作是update的话if exists (select * from inserted) and exists (select * from deleted)
begin
update parts set qty=(select inqty from inserted) where partno in (select partno from inserted)
end