22,210
社区成员
发帖
与我相关
我的任务
分享
if exists(select * from sysobjects where name = 'A' and xtype = 'U')
drop table A
if exists(select * from sysobjects where name = 'B' and xtype = 'U')
drop table B
create table A(
id nvarchar(2),
店名 nvarchar(4),
日期 datetime,
応収金額 money,
実収金額 money
)
insert into A values('01','001','2004-12-26 10:54:37.000',10,10)
insert into A values('02','001','2004-12-26 11:54:37.000',11,10)
insert into A values('03','002','2004-12-26 12:54:37.000',12,12)
create table B(
id nvarchar(2),
商品编码 nvarchar(4),
销售数量 int,
销售价 money,
实收金额 money
)
insert into B values('01','1001',1,10,10)
insert into B values('02','1002',1,5.5,5.5)
insert into B values('02','1003',1,5.5,5.5)
insert into B values('03','1004',1,12,12)
if exists(select * from sysobjects where name = 'dd' and xtype = 'tr')
drop trigger dd
go
create trigger dd on B for delete
as
declare @id nvarchar(2),@money1 money,@money2 money
select @id = id from deleted
select @money1 = 销售价 from deleted
select @money2 = 实收金额 from deleted
print @id
print @money1
print @money2
update A
set 応収金額 = 応収金額 - @money1,実収金額 = 実収金額 - @money2
where A.id = @id
go
--test date
select * from A
delete from B where 商品编码 = '1003'
select * from A