USE [Yolo880_Hotel]
GO
/****** Object: Trigger [dbo].[change_goods] Script Date: 2024-01-30 14:12:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[change_goods]
on [dbo].[T_Goods]
AFTER update
AS
BEGIN
Declare @old_id int; --原来的商品id
Declare @old_num int; --原来的数量
Declare @old_name varchar(20); -- 原来的名称
Declare @old_type varchar(8); --原来的类型
Declare @old_ns varchar(8); --原来的数字码
Declare @old_price varchar(8); --原来的价格
Declare @old_cprice varchar(8); --原来的成本
Declare @new_num int; --修改后的数量
Declare @new_name varchar(20); -- 修改后的名称
Declare @new_type varchar(8); --修改后的类型
Declare @new_ns varchar(8); --修改后的数字码
Declare @new_price varchar(8); --修改后的价格
Declare @new_cprice varchar(8); --修改后的成本价格
select @old_id = F_ID, @old_num = F_Number,@old_type= F_SalesType ,@old_name= F_GoodsName,@old_ns= F_NumberSpell,@old_price=F_RealWages,@old_cprice=F_CostPrice FROM deleted --先获取原商品的ID
select @new_num = F_Number,@new_type= F_SalesType ,@new_name= F_GoodsName,@new_price= F_RealWages,@new_cprice=F_CostPrice FROM inserted --先获取商品的ID
Declare @id int; --原来的商品id
--1.先获取更新前商品的名称，类型，和数字码 @old_name,@old_type,@old_ns
--2.从T_IOS_Goods表里面获取
select @id =F_ID from T_IOS_Goods where F_NumberSpell = @old_ns and F_SalesType = @old_type and F_GoodsName =@old_name
IF UPDATE(F_Number) and (@old_num <> @new_num)
BEGIN
INSERT INTO dbo.t_log_system
(F_EventType ,F_Content,F_UserName)
SELECT '修改货品库存','商品名称：'+F_GoodsName+' 商品类型：'+F_SalesType+' 库存从：'+(SELECT CAST(F_Number AS varchar) AS Expr1 FROM deleted)+ '-》'+
CAST(F_Number AS varchar) + ' ,差额：'+CAST(F_Number-(SELECT F_Number FROM deleted) 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) and (@old_type<>@new_type ))
BEGIN
INSERT INTO dbo.t_log_system
(F_EventType ,F_Content,F_UserName)
SELECT '修改商品类型','商品名称：'+F_GoodsName+'：类型从：'+(SELECT F_SalesType FROM deleted)+ '-》'+ +F_SalesType
,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_GoodsName) and (@old_name<>@new_name ))
begin
if (@id is not null)
begin
update T_IOS_Goods set F_GoodsName = @new_name where F_Id = @id
INSERT INTO dbo.t_log_system
(F_EventType ,F_Content,F_UserName)
SELECT '同步进销存商品名称','商品名称：'+F_GoodsName+'：名称从：'+@old_name+ '-》'+@new_name
,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
--更新商品的售价，用于和进销存数据同步
IF (UPDATE(F_RealWages) and (@old_price<>@new_price ))
begin
if (@id is not null)
begin
update T_IOS_Goods set F_RealWages = @new_price,F_SalePrice=@new_price where F_ID = @id
INSERT INTO dbo.t_log_system
(F_EventType ,F_Content,F_UserName)
SELECT '同步进销存商品价格','商品名称：'+F_GoodsName+'：价格从：'+@old_price+ '-》'+@new_price
,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
--更新商品的成本，用于和进销存数据同步
IF (UPDATE(F_CostPrice) and (@old_cprice<>@new_cprice ))
begin
if (@id is not null)
begin
update T_IOS_Goods set F_CostPrice = @new_cprice where F_ID = @id
INSERT INTO dbo.t_log_system
(F_EventType ,F_Content,F_UserName)
SELECT '同步进销存商品成本','商品名称：'+F_GoodsName+'：价格从：'+@old_cprice+ '-》'+@new_cprice
,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
END
