Question

SQL中使用触发器实现两张表之间的加减问题

假如有两张表 一张为A 另一张为B A中有数量 B也有数量 当A B中有同一物品 且当A 数量变化 B中同一物品数量变化为 原始数量 - A数量

Answer 1

create table TEST_A
( NO CHAR(2) NOT NULL ,

QTY [numeric](6, 2) NULL )
-- drop table TEST_B
create table TEST_B
( NO CHAR(2) NOT NULL ,
NO2 CHAR(2) NOT NULL ,
QTY [numeric](6, 2) NULL )
INSERT TEST_B VALUES('1','2',5)
select * from TEST_B
select * from TEST_a
delete from TEST_B
delete from TEST_a
update d set d.qty = d.qty - 1 from TEST_b d where d.NO2 = '2'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
alter trigger [dbo].[tr_TEST_B_i] on [dbo].[TEST_B] for insert,delete as
set nocount on
if object_id('tempdb..#disable_TEST_B') is not null return
begin tran
if exists(select 1 from inserted a , TEST_A b where a.no = b.no)
update b set b.qty = isnull(b.qty,0) + a.qty from inserted a ,TEST_A b where a.no = b.no
if @@error <> 0
begin
rollback
raiserror('update error!',16,1)
return
end
if exists(select 1 from deleted a , TEST_A b where a.no = b.no)
update b set b.qty = b.qty - a.qty from deleted a ,TEST_A b where a.no = b.no
if @@error <> 0
begin
rollback
raiserror('delete error!',16,1)
return
end

INSERT TEST_A (no,qty) select a.no,a.qty from inserted a where not exists(select 1 from TEST_A b where a.no = b.no)
if @@error <> 0
begin
rollback
raiserror('insert error!',16,1)
return
end
commit

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
alter trigger [dbo].[tr_TEST_B_u] on [dbo].[TEST_B] for update as
set nocount on
if object_id('tempdb..#disable_TEST_B') is not null return
if update(no)
begin
rollback
raiserror('buneng xiugai no!',16,1)
return
end
IF(Update(qty))
begin
DECLARE @SL decimal(6,2) --修改前数量
DECLARE @DHSL decimal(6,2) --修改后数量
declare @no char(2)
DECLARE @upSL decimal(6,2)
SELECT @no = no ,@SL=qty FROM Deleted
SELECT @DHSL=qty FROM INSERTED
set @upSL = @DHSL - @SL
begin tran
update b set b.qty = isnull(b.qty,0) + @upSL from TEST_A b where b.no = @no
if @@error <> 0
begin
rollback
raiserror('update error!',16,1)
return
end
commit
end

追问

可以解一下吗 真心看不懂

追答

明天白天的