USE [Yolo880_Hotel]
GO
/****** Object: Trigger [dbo].[change_goods] Script Date: 2023-01-06 10:06:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[change_goods]
on [dbo].[T_Goods]
AFTER update
AS
BEGIN
IF UPDATE(F_Number)
BEGIN
Declare @old int; --原来的数量
Declare @goodsname varchar(8); --商品名称
Declare @goodsid varchar(8); --原来的数量
Declare @goodsnum varchar(20); --原来的数量
select @old = F_Number,@goodsname=F_GoodsName FROM deleted;
--select @id = F_ID from inserted
select @goodsid = F_ID from T_IOS_Goods where F_GoodsName = @goodsname;
--70000001需要修改为自己库中的前厅仓库id，可以在软件中查看
select @goodsnum = F_StoreNumber FROM T_IOS_Store where F_Ptfid = @goodsid and F_Storefid = '70000001';
if(@goodsnum is null)
begin
set @goodsnum='无此商品数据'
end
INSERT INTO dbo.t_log_system
(F_EventType ,F_Content,F_UserName)
SELECT F_GoodsName,'库存变化，商品类型：'+F_SalesType+' ,商品名称：'+F_GoodsName+' ,现库存：'+CAST(F_Number AS varchar)+ ' ,原库存：'+
(SELECT CAST(F_Number AS varchar) AS Expr1 FROM deleted) + ' ,差额：'+CAST(F_Number-(SELECT F_Number FROM deleted) AS varchar)+' 仓库数量减少前是：'+CAST(@goodsnum AS varchar),
b.F_UserName FROM inserted , (select u.F_UserName, t.F_HostName, t.F_HostIP from t_userpower u inner join(
SELECT top 1 F_UserName, F_HostName, F_HostIP from T_LoginSysLog where f_hostname =
HOST_NAME() and f_leaveinfo is null order by F_ID desc) t on u
.F_UserName = t.F_UserName and u.F_Status = '上班') b
END
IF UPDATE(F_SalesType) or UPDATE(F_SalePrice)
BEGIN
INSERT INTO dbo.t_log_system
(F_EventType ,F_Content,F_UserName)
SELECT '修改商品数据','商品类型：'+F_SalesType+ ' ,原类型：'+(SELECT F_SalesType FROM deleted)
+' ,商品价格：'+CAST(F_SalePrice AS varchar)+ ' ,原价格：'+(SELECT CAST(F_SalePrice AS varchar) AS Expr1 FROM deleted)
,b.F_UserName FROM inserted , (select u.F_UserName, t.F_HostName, t.F_HostIP from t_userpower u inner join(
SELECT top 1 F_UserName, F_HostName, F_HostIP from T_LoginSysLog where f_hostname =
HOST_NAME() and f_leaveinfo is null order by F_ID desc) t on u.F_UserName = t.F_UserName and u.F_Status = '上班') b
END
END
CREATE trigger [dbo].[change_IOS_Store]
on [dbo].[T_IOS_Store]
AFTER update
AS
BEGIN
IF UPDATE(F_StoreNumber)
BEGIN
Declare @old int; --原来的数量
Declare @goodsname varchar(20); -- 商品名称
Declare @goodsid varchar(8); --原来的数量
Declare @goodsnum int; --商品库存
Declare @Storefid int; --仓库id
Declare @Company varchar(20); --仓库名称
select @goodsid = F_Ptfid, @goodsnum = F_StoreNumber,@Storefid= F_Storefid FROM deleted --先获取商品的ID
select @goodsname = F_GoodsName from T_IOS_Goods where F_ID = @goodsid;
select @Company = F_Name from T_IOS_Company where F_ID = @Storefid;
if(@goodsname is null)
begin
set @goodsname='无此商品数据'
end
INSERT INTO dbo.t_log_system
(F_EventType ,F_Content,F_UserName)
SELECT @Company+':'+@goodsname,'进销存商品库存变化：'+@goodsname+' 现库存：'+CAST(F_StoreNumber AS varchar)+ ' 原库存：'+CAST(@goodsnum AS varchar)+' 差额：'
+cast(F_StoreNumber-@goodsnum as varchar) ,
b.F_UserName FROM inserted , (select u.F_UserName, t.F_HostName, t.F_HostIP from t_userpower u inner join(
SELECT top 1 F_UserName, F_HostName, F_HostIP from T_LoginSysLog where f_hostname =
HOST_NAME() and f_leaveinfo is null order by F_ID desc) t on u
.F_UserName = t.F_UserName and u.F_Status = '上班') b
END
END
