五月十一号，仓库进销存，出库触发器

check_出库表

CREATE TRIGGER [dbo].[check_出库]
ON [dbo].[出库表]
AFTER INSERT
AS
DECLARE @wpbianhao int,@ygbianhao int,@cksl int
SELECT @wpbianhao=物品编号 FROM inserted
SELECT @ygbianhao=员工编号 FROM inserted
SELECT @cksl=出库数量 FROM inserted
IF EXISTS
(SELECT * FROM 库存信息表 WHERE 物品名称=(SELECT 物品名称 FROM 物品信息表 WHERE @wpbianhao=物品编号))
BEGIN
	IF EXISTS
	(SELECT 员工姓名 FROM 员工信息表 WHERE @ygbianhao=员工编号)
	BEGIN
		declare @a int
		SELECT @a=剩余数量 FROM 库存信息表 WHERE 物品名称=(SELECT 物品名称 FROM  物品信息表 WHERE 物品编号=@wpbianhao)
		IF (@a-@cksl)>0
		BEGIN
			UPDATE 库存信息表
			SET 剩余数量=剩余数量-@cksl
			where 物品名称=(SELECT 物品名称 FROM 物品信息表 WHERE @wpbianhao=物品编号)
		END
		ELSE
		BEGIN
			PRINT'物品不足'
			ROLLBACK
		END
		
	END
	ELSE
	BEGIN
		PRINT'员工不存在'
		ROLLBACK
	END
	
END
ELSE
BEGIN
	PRINT'物品不存在，或者无库存'
	ROLLBACK
END

check_删除_出库

CREATE TRIGGER [dbo].[check_删除_出库]
ON [dbo].[出库表]
AFTER DELETE
AS
BEGIN
DECLARE @wpbianhao int,@cksl int
SELECT  @wpbianhao = 物品编号,@cksl = 出库数量 FROM DELETED
DECLARE @name nvarchar(10)
SELECT @name = 物品名称 FROM 物品信息表 WHERE 物品编号=@wpbianhao
UPDATE 库存信息表
SET 剩余数量=剩余数量+@cksl
WHERE 物品名称 = @name
END

check_修改_出库

ALTER TRIGGER [dbo].[check_修改_出库]
   ON  [dbo].[出库表]
   AFTER UPDATE
AS 
BEGIN
	DECLARE @wpbianhao_qian int,@ygbianhao_qian int,@cksl_qian int
	DECLARE @wpbianhao_hou int,@ygbianhao_hou int ,@cksl_hou int
	SELECT @wpbianhao_qian=物品编号,@ygbianhao_qian=员工编号,@cksl_qian=出库数量 FROM DELETED
	SELECT @wpbianhao_hou=物品编号,@ygbianhao_hou=员工编号,@cksl_hou=出库数量 FROM INSERTED

	IF @wpbianhao_qian=@wpbianhao_hou and @ygbianhao_qian=@ygbianhao_hou
	BEGIN
		DECLARE @a int
		SELECT @a=剩余数量 FROM 库存信息表 WHERE 物品名称=(SELECT 物品名称 FROM 物品信息表 WHERE 物品编号=@wpbianhao_qian)
		IF @a+@cksl_qian-@cksl_hou>0
		BEGIN
			UPDATE 库存信息表
			SET 剩余数量=@a+@cksl_qian-@cksl_hou
			WHERE 物品名称=(SELECT 物品名称 FROM 物品信息表 WHERE 物品编号=@wpbianhao_qian)
		END
		ELSE
		BEGIN
			PRINT'库存不足'
			ROLLBACK
		END
	END
	ELSE
	BEGIN
	
		DECLARE @name nvarchar(10)
		SELECT @name = 物品名称 FROM 物品信息表 WHERE 物品编号=@wpbianhao_qian
		UPDATE 库存信息表
		SET 剩余数量=剩余数量+@cksl_qian
		WHERE 物品名称 = @name  --恢复删除掉的数据
	
		IF EXISTS
	(SELECT * FROM 库存信息表 WHERE 物品名称=(SELECT 物品名称 FROM 物品信息表 WHERE @wpbianhao_hou=物品编号))
	BEGIN
		IF EXISTS
		(SELECT 员工姓名 FROM 员工信息表 WHERE @ygbianhao_hou=员工编号)
		BEGIN
			declare @b int
			SELECT @b=剩余数量 FROM 库存信息表 WHERE 物品名称=(SELECT 物品名称 FROM  物品信息表 WHERE 物品编号=@wpbianhao_hou)
			IF (@b-@cksl_hou)>0
			BEGIN
				UPDATE 库存信息表
				SET 剩余数量=剩余数量-@cksl_hou
				where 物品名称=(SELECT 物品名称 FROM 物品信息表 WHERE @wpbianhao_hou=物品编号)
			END
			ELSE
			BEGIN
				PRINT'物品不足'
				ROLLBACK
			END
			
		END
		ELSE
		BEGIN
			PRINT'员工不存在'
			ROLLBACK
		END
		
	END
	ELSE
	BEGIN
		PRINT'物品不存在，或者无库存'
		ROLLBACK
	END
	END
END