CREATE TABLE 卷烟销售表
(
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,
购货商 VARCHAR(40) NULL,
销售数量 INT NULL,
销售单价 MONEY NULL,
销售金额 MONEY NULL
)
GO
--业务规则：库存金额 = 库存数量 * 库存单价 业务规则。
CREATE TABLE 卷烟库存表
(
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,
库存数量 INT NULL,
库存单价 MONEY NULL,
库存金额 MONEY NULL
)
GO
--触发器功能： 强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷烟库存表')
DROP TRIGGER T_INSERT_卷烟库存表
GO
CREATE TRIGGER T_INSERT_卷烟库存表
ON 卷烟库存表
FOR INSERT
AS
--提交事务处理
BEGIN TRANSACTION
--强制执行下列语句，保证业务规则
UPDATE 卷烟库存表
SET 库存金额 = 库存数量 * 库存单价
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED)
COMMIT TRANSACTION
GO
--测试T_INSERT_卷烟库存表触发器
INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
SELECT '红塔山新势力',100,12,1200 UNION ALL
SELECT '红塔山人为峰',100,22,NULL UNION ALL
SELECT '云南映像',100,60,500 UNION ALL
SELECT '玉溪',0,30,0
GO
--触发器功能： 实现业务规则。
--业务规则: 如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。
--否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷烟销售表')
DROP TRIGGER T_INSERT_卷烟销售表
GO
CREATE TRIGGER T_INSERT_卷烟销售表
ON 卷烟销售表
FOR INSERT
AS
BEGIN TRANSACTION
--检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零
IF NOT EXISTS (
SELECT 库存数量
FROM 卷烟库存表
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)
)
BEGIN
--返回错误提示
RAISERROR('错误！该卷烟不存在库存，不能销售。',16,1)
--回滚事务
ROLLBACK
RETURN
END
IF EXISTS (
SELECT 库存数量
FROM 卷烟库存表
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND
库存数量 <= 0
)
BEGIN
--返回错误提示
RAISERROR('错误！该卷烟库存小于等于0，不能销售。',16,1)
--回滚事务
ROLLBACK
RETURN
END
--对合法的数据进行处理
--强制执行下列语句，保证业务规则
UPDATE 卷烟销售表
SET 销售金额 = 销售数量 * 销售单价
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)
DECLARE @卷烟品牌 VARCHAR(40)
SET @卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED)
DECLARE @销售数量 MONEY
SET @销售数量 = (SELECT 销售数量 FROM INSERTED)
UPDATE 卷烟库存表
SET 库存数量 = 库存数量 - @销售数量,
库存金额 = (库存数量 - @销售数量)*库存单价
WHERE 卷烟品牌 = @卷烟品牌
COMMIT TRANSACTION
GO
--测试T_INSERT_卷烟销售表触发器
--请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。
--针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT '红塔山新势力','某购货商',10,12,1200
GO
--针对[卷烟销售表]，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量。
--触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT '红塔山人为峰','某购货商',10,22,2000
GO
--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应。
--触发器将报错。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT '红河V8','某购货商',10,60,600
GO