create tigger test on 销售表 after insert as beginselect i.销售数量*(i.销售单价-a.单价) as 总毛利 from 商品表 a join inserted i on a.商品号=i.商品号end --毛利表在这里起什么作用
tryCREATE TRIGGER tri_test ON 销售表 AFTER INSERT AS BEGIN SET NOCOUNT ON; declare @sell_price int,@product_price int declare @product_code varchar(32) declare @sell_qty int select @sell_price =sell_price,@sell_qty=qty,@product_code=product_code from inserted select @product_price=product_price from 商品表 where product_code=@product_code if exists (select 1 from 毛利表 where product_code=@product_code) begin update 毛利表 set 总毛利=总毛利+@sell_qty*(@sell_price-@product_price) where product_code=@product_code end else begin insert into 毛利表 select @product_code,@sell_qty*(@sell_price-@product_price) end END GO
after insert
as
beginselect i.销售数量*(i.销售单价-a.单价) as 总毛利 from 商品表 a join inserted i on a.商品号=i.商品号end
--毛利表在这里起什么作用
ON 销售表
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON; declare @sell_price int,@product_price int
declare @product_code varchar(32)
declare @sell_qty int
select @sell_price =sell_price,@sell_qty=qty,@product_code=product_code from inserted
select @product_price=product_price from 商品表 where product_code=@product_code
if exists (select 1 from 毛利表 where product_code=@product_code)
begin
update 毛利表 set 总毛利=总毛利+@sell_qty*(@sell_price-@product_price) where product_code=@product_code
end
else
begin
insert into 毛利表 select @product_code,@sell_qty*(@sell_price-@product_price)
end
END
GO